SQL Query, get all ids that do not have a given status value

I have a table called statuses shown below. Every time a user logs in a row is added. A status is added. I need to write a query that gives me all IDs one time that have never been 1, 6 or 8.

id status
1 1
1 6
1 8
1 1
2 1
2 6
2 8
2 0
3 0
3 0
3 0
3 0

I wrote a query:

SELECT DISTINCT(id) FROM statuses WHERE status NOT IN (1, 6, 8)

Unfortunately this gives me ID 2 and 3, but I don’t want ID 2 to be selected because they have statuses 1, 6, and 8.

Thanks for your help!

Answer

You can group by id and set the condition in the HAVING clause:

SELECT id 
FROM statuses 
GROUP BY id
HAVING SUM(status IN (1, 6, 8)) = 0

Or:

SELECT DISTINCT id 
FROM statuses 
WHERE id NOT IN (SELECT id FROM statuses WHERE status IN (1, 6, 8))

See the demo.

Leave a Reply

Your email address will not be published. Required fields are marked *