例如,如果我有球和盒子,球可以放在许多盒子里,盒子可以锁上,我如何选择不在锁上的盒子里的球?

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/

10-12 04:09