I have 2 tables task and taskattributes. There is a linking between 2 tables with taskid. Each taskid has multiple attributes represented by key,value.
I would like to find out if specific key exists for the task
For e.g. here if I want to check all the tasks which do not have key 'A'.
use correlated subquery with not exists
select a.taskid, b.key, b.value
from task a inner join taskattributes b on a.taskid=b.taskid
where not exist
(select 1 from taskattributes c on c.taskid=b.taskid and key='A')
from clause. Arbitrary letters are quite hard to follow, because the reader has to continually refer to the from clause to figure out where the columns come from.
With not exists:
select *
from task t
where not exists (
select 1 from taskattributes
where taskid = t.taskid and key = 'A'
)
One simple solution uses aggregation:
SELECT
t.taskid,
t.name
FROM task t
INNER JOIN taskattributes ta
ON t.taskid = ta.taskid
GROUP BY
t.taskid,
t.name
HAVING
COUNT(CASE WHEN "key" = 'A' THEN 1 END) = 0;
If you are using Postgres 9.4 or later, you may use FILTER in the HAVING clause:
HAVING COUNT(*) FILTER (WHERE "key" = 'A') = 0