jemminger jemminger - 6 days ago 5
SQL Question

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

Answer

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
                 );
Comments