Search code examples
sqlgroup-by

SQL to select items according key where none of items under the key fulfil condition

I have a table with items like

id | value
---+-----
1  | 'aaa'
1  | 'bbb'
2  | 'bbb'
2  | 'ccc'
3  | 'bbb'

How to select items which has more than one rows under ID and none of the value in column value for this ID equals 'aaa'? I dont understand how to exclude items with ID 1 which contains more than one item but have one item which value is 'aaa'. Result should be only items with ID 2.

Solution

  • I'm not sure I understood the question correctly, but I think you want to get the IDs that appear more than once and then exclude the ones that contain 'aaa'. This is one way to do it:

    SELECT id
FROM table
WHERE id NOT IN (SELECT id FROM TABLE WHERE value = 'aaa')
GROUP BY id
HAVING COUNT(*) > 1