CODEWITHSUNDEEP
mysqlsql
almog50
almog50

Reputation: 75

SQL Find Get Item that does not have specific status

I have the following table:

case_id | run_id | status_id
1       | 44     |  1
1       | 45     |  3
1       | 46     |  1
2       | 44     |  3
2       | 45     |  3
2       | 46     |  3

The table above is the results of the following query:

SELECT t.case_id, t.run_id, t.status_id
FROM `test` t,
(SELECT id FROM `run` WHERE created_on BETWEEN '2019-10-01' AND CURRENT_DATE()) r
WHERE t.run_id = r.id

I'm trying to select only the case_ids that have only 3 in the status_id.

so the query should return case_id 2.

Upvotes: 0

Views: 51

Answers (2)

GMB
GMB

Reputation: 222592

You can use aggregation and filter with a having clause:

select t.case_id
from test t
inner join run r on r.id = t.run_id
where r.created_on between '2019-10-01' and current_date()
group by t.case_id
having count(r.status_id <> 3) = 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270613

You can use proper, explicit, standard JOIN syntax . . . and then aggregation and filter with a HAVING clause:

select t.case_id
from test t join
     run r
     on t.run_id = r.id 
where r.created_on BETWEEN '2019-10-01' AND CURRENT_DATE()
group by t.case_id
having min(t.status_id) = max(t.status_id) and min(t.status_id) = 3;

Upvotes: 3

Related Questions