There are pairs (type_id, element_id), like (1,1), (1,2), .. (5,3). How to exclude type_id from result if I need to get type ids without element_id 1?

user_17614576 0
Viewed 26

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].

where-clause postgresql
1 Answers

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

sqlfiddle

Or using filter clause with aggregate function

SELECT type_id 
FROM x
GROUP BY type_id 
HAVING COUNT(*) FILTER (WHERE element_id = 1 ) = 0
user_51760710
Related