I have the next table x
:
id | type_id | element_id
-------------------------
1 | 1 | 1
2 | 1 | 2
3 | 1 | 3
4 | 2 | 1
5 | 3 | 1
6 | 4 | 2
7 | 5 | 3
I need to get all type ids which meet condition element_id != 1
, in other words how to get the next type_ids: [4, 5]?
If I simply do SELECT type_id FROM x WHERE element_id != 1
, I receive [1, 1, 4, 5].
CodePudding user response:
You can try to use the condition aggregate function in HAVING
which didn't contain any element_id = 1
from type_id
SELECT type_id
FROM x
GROUP BY type_id
HAVING COUNT(CASE WHEN element_id = 1 THEN 1 END) = 0
Or using filter clause with aggregate function
SELECT type_id
FROM x
GROUP BY type_id
HAVING COUNT(*) FILTER (WHERE element_id = 1 ) = 0