CODEWITHSUNDEEP
sql
Attiq
Attiq

Reputation: 165

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

Upvotes: 1

Views: 3537

Answers (3)

Sagar Shelke
Sagar Shelke

Reputation: 517

This should give you the expected result:

select *
from table t1
where status in (1,3)
and not EXISTS
(
    select * from table t2 where t2.id = t1.id and t2.status = 4
)

Upvotes: 0

M T Head
M T Head

Reputation: 1290

The query may need to be changed depending on what Sql version you are looking for but if I understand what you are asking you only expect to get back the 1112 and the 1114 records based on what you described. If that is correct here is my version of your query request.

select Id, Status, Iteration from table where Id not in (select Id from table where Status = 4 )

Upvotes: 0

snow_FFFFFF
snow_FFFFFF

Reputation: 3301

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!

Upvotes: 4

Related Questions