例如,如果我有球和盒子,球可以放在许多盒子里,盒子可以锁上,我如何选择不在锁上的盒子里的球?
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
我已经用左连接提出了这个,但是它返回了“in-both”球
我想排除。
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
)
预期结果:
id name
== ====
1 in neither
2 in unlocked
SQL小提琴:
http://sqlfiddle.com/#!9/c26ab/4
最佳答案
我想你的意思是:选择那些永远不会被锁在盒子里的球。
如果是,就会想到一个not exists
查询:
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
);
关于mysql - 不包括列值的联接记录?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/40876963/