Attiq Attiq - 3 months ago 9
SQL Question

SQL Query filter by status

I had the following sort of table:

id status iteration
---- ------- ----------
1111 1 1
1111 3 2
1111 4 3
1112 1 1
1113 1 1
1113 4 2
1114 1 1


the problem i am facing is trying to make a query with the IDs whose status is 1 or 3. If the status is ever 4, we should remove that id from the query.

I had made the following query but i can only seem to get the results which have been 1 or 3. if the same id has a status of 4, i can't filter it out.

Select id,status, iteration
from table
WHERE status IN (1,3) ;


Any help is greatly appreciated

Answer

I think this is what you want:

SELECT id, status, iteration
FROM table
WHERE id NOT IN (SELECT id FROM table WHERE status=4)

...which just excludes the 4's. If you truly just want 1's and 3's and exclude everything else:

SELECT id, status, iteration
FROM table
WHERE id NOT IN (SELECT id FROM table WHERE status NOT IN (1, 3))

thanks to @sstan for catching some bad logic in the second example!