SQL query with having

Question

I've some problems with understanding how having works. It's clear to me when I check the count of something, but is this the only use case for it?

For example I have a tasks table and tasks_parameters table which have got columns: key, value, task_id. Now, I would like to select all tasks which have got two parameters:

key = 'type' && value = 'work'
key = 'priority' && value = 'normal'

I was thinking about that query:

select t.id from tasks t
join tasks_parameters p on (p.task_id=t.id)
group by t.id, p.key, p.value
having p.key = 'type' and p.value = 'work' and
       p.key = 'priority' and p.value = 'normal';

but, ofcourse, it doesn't work, because parameter can't have two diffrent values for the key. Is it even possible to query something like this?

Answer 1

You are close. But, you need two changes:

• Remove key and value from the group by.
• Change the having to use sum() instead of the unaggregated values.

The resulting query looks like:

select t.id
from tasks t join
     tasks_parameters p
      on p.task_id = t.id
group by t.id
having sum(case when p.key = 'type' and p.value = 'work' then 1 else 0 end) > 0 and
       sum(case when p.key = 'priority' and p.value = 'normal' then 1 else 0 end) > 0;

The having clause takes place after the group by. The sum() counts the number of rows that match each condition.

Answer 2

The having clause is for filtering on aggregate results. To filter on field values, use a where clause.

select t.id 
from tasks t join tasks_parameters p on p.task_id=t.id
where (p.key = 'type' and p.value = 'work')
or
(p.key = 'priority' and p.value = 'normal');