jemminger jemminger - 1 year ago 75
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?

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

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

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 =
LEFT OUTER JOIN boxes ON = boxings.box_id
WHERE (boxings.box_id IS NULL or boxes.locked = 0)
SELECT id FROM boxes WHERE locked = 1

Desired results:

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

SQL Fiddle:!9/c26ab/4

Answer Source

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 =
                  where bxb.ball_id = and bo.locked = 1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download