CODEWITHSUNDEEP
mysql
Ivan T.
Ivan T.

Reputation: 535

SELECT rows where column value is NEVER x

I have a table which is linked to another table. One table holds carriers, and another holds routes for the carriers. The routes table has a carrier_id column which ties one with another, and an status field which determines the route's activity status, 0 being the active status.

I am trying to list carriers which have no active routes, and this has put me in a precarious situation, where carriers without routes at all are selected easily, but the second party of the query causes trouble for me.

SELECT c.id
     , c.title 
  FROM carriers c
  LEFT 
  JOIN routes r
    ON r.carrier_id = c.id
WHERE r.route_id IS NULL 
    OR (r.status > 0 AND r.carrier_id = c.id)

The problem with this is fairly obvious - the resulting table gets false positives - carriers who have archived AND unarchived routes. I'm pretty sure SQL employs some sort of construction I can use to specify something like this:

if any of carrier.routes.status == 0 exclude carrier from selection

And this is pretty much what the question boils down to.

Update: I've been asked for a dataset and an expected result for that data set, so I'm providing it below:

carriers:

--------------
| id | title |
--------------
|  1 | foo   |
|  2 | bar   |
|  3 | baz   |
--------------

routes:

----------------------------
| id | carrier_id | status |
----------------------------
|  1 |     1      |    0   |
|  2 |     1      |    1   |
|  3 |     2      |    1   |
----------------------------

With the following data, carriers 2 and 3 should be returned, seeing as 3 has no active routes, and neither has 2. 1, however, has an active route, and thus is excluded from this selection.

Upvotes: 1

Views: 893

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30595

Try like this 

SELECT carrier.id, carrier.title
FROM carriers LEFT JOIN routes
   ON routes.carrier_id = carrier.id and 
 (route.route_id IS NULL OR (route.status > 0)

Please note that Where (route.route_id IS NULL OR (route.status > 0) clause implicitly converts your left join to inner join

Better and cleaner solution

Select * from carriers
Where exists 
(
    Select 1 from routes where routes.carrier_id = carrier.id and status != 0
) or carriers.route_id is null.

OP's note: what I actually found working for me is based on the logic above, and goes like this:

Select * from carriers
Where exists 
(
    Select 1 from routes where routes.carrier_id = carrier.id and status != 0
) and not exists
(
       Select 1 from routes where routes.carrier_id = carrier.id and status != 0
) or carriers.route_id is null.

Upvotes: 3

Sonal Borkar
Sonal Borkar

Reputation: 561

You may need to try Left Outer Join which will give those records in left side table not present in the right side. So, all the Carriers with no records will be displayed. Now you just need to add one condition of routes.status > 0 for records with routes having active status. Like below:

SELECT carrier.id, carrier.title FROM carriers
LEFT JOIN routes ON routes.carrier_id = carrier.id
WHERE routes.status > 0

Upvotes: 0

Related Questions