CODEWITHSUNDEEP
mysqlsqlpostgresqljoin
jemminger
jemminger

Reputation: 5173

Excluding joined records on column value?

For example, if I have Balls and Boxes, balls can be in many boxes, and boxes can be locked, how can I select the balls that are not in a locked box?

balls
  id  name
  ==  ====
  1   in neither
  2   in unlocked
  3   in locked
  4   in both

boxes
  id  locked
  ==  ======
  1   0
  2   1

boxings
  ball_id box_id
  ======= ======
  2       1
  3       2
  4       1
  4       2

I've come up with this using left joins, but it's returning the "in both" ball which I want to exclude.

SELECT balls.* 
FROM balls 
LEFT OUTER JOIN boxings ON boxings.ball_id = balls.id 
LEFT OUTER JOIN boxes ON boxes.id = boxings.box_id 
WHERE (boxings.box_id IS NULL or boxes.locked = 0)
  AND boxes.id NOT IN (
    SELECT id FROM boxes WHERE locked = 1
    )

Desired results:

id  name
==  ====
1   in neither
2   in unlocked

SQL Fiddle: http://sqlfiddle.com/#!9/c26ab/4

Upvotes: 2

Views: 38

Answers (3)

onedaywhen
onedaywhen

Reputation: 57023

I find it interesting that both answers already posted both take the same slightly odd approach of putting some of the join criteria in the WHERE clause:

inner join boxes on boxes.id = boxings.box_id
where boxes.locked = 1

What is most odd is that I'm sure the same people advise elsewhere to not put join conditions in WHERE clauses.

Anyway, here's a different variation on the anti-join approach:

SELECT *
  FROM balls
 WHERE id NOT IN ( SELECT ball_id
                     FROM boxings
                          NATURAL JOIN 
                          ( SELECT id AS box_id FROM boxes WHERE locked = 1 )
                          NATURAL JOIN 
                          ( SELECT id AS ball_id FROM boxings ) );

Upvotes: 0

Ike Walker
Ike Walker

Reputation: 65537

The most straightforwad way to do this is probably with NOT EXISTS:

select id, name 
from balls 
where not exists (
  select NULL 
  from boxings 
  inner join boxes on boxes.id = boxings.box_id 
  where boxes.locked = 1
  and boxings.ball_id = balls.id
)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269553

I assume you mean: choose balls that are never in a locked box.

If so, a not exists query comes to mind:

select b.*
from balls b
where not exists (select 1
                  from boxings bxb join
                       boxes bo
                       on bxb.box_id = bo.id
                  where bxb.ball_id = b.id and bo.locked = 1
                 );

Upvotes: 2

Related Questions