CODEWITHSUNDEEP
mysqlsqldatabasestatus
THE DOCTOR
THE DOCTOR

Reputation: 4555

SQL Query for No Active Status

Scenario: I have a table which has a status of active or deleted. Let's say we are talking about car owners. The status in the table can reflect the fact that there were 2 previous owners (i.e. - deleted status) and that there should be only 1 current owner which has an "active" status in the table.

Question: How would I write a query involving a table like this to only include results where there is no current owner (i.e. - no row for that car with an "active" status)?

The following will only match if there's at least 1 row which matches the condition stated above. It is therefore not valid for what I'm seeking:

Test query 1

SELECT *
FROM car_owners
WHERE status = 'deleted'

Test query 2

SELECT *
FROM car_owners
WHERE status != 'active'

Upvotes: 1

Views: 1442

Answers (2)

DxTx
DxTx

Reputation: 3357

You can use NOT IN operator for this.

Table Structure and Sample data

CREATE table car (
  carid int, 
  ownerid int, 
  carstatus varchar(50)
);
INSERT INTO car 
VALUES 
  (1, 1, "active"), 
  (2, 3, "active"), 
  (3, 5, "active"), 
  (4, 11, "deleted"), 
  (4, 12, "deleted"), 
  (4, 23, "active"), 
  (5, 13, "deleted"), 
  (6, 35, "deleted"), 
  (6, 38, "deleted"), 
  (6, 53, "deleted"), 
  (6, 10, "active"), 
  (7, 13, "deleted"), 
  (8, 16, "deleted");

SQL Query

SELECT *
FROM   car
WHERE  carid NOT IN (SELECT carid
                     FROM   car
                     WHERE  carstatus = "active")

Output

+--------+----------+-----------+
| carid  | ownerid  | carstatus |
+--------+----------+-----------+
|     5  |      13  | deleted   |
|     7  |      13  | deleted   |
|     8  |      16  | deleted   |
+--------+----------+-----------+

Online Demo: http://sqlfiddle.com/#!9/0746c1/9/0

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270593

I think you want:

select co.*
from car_owners co
where not exists (select 1
                  from car_owners co2
                  where co2.car_id = co.car_id and co2.status = 'active'
                 );

If you just want the car_ids, you can use aggregation:

select co.car_id
from car_owners co
group by co.car_id
having not max( status = 'active' );

You can also phrase the having as:

having min(status) = 'deleted'

Upvotes: 3

Related Questions