在MySQL中使用“不存在"的SQL Division [英] SQL Division using 'not exists' in mysql

问题描述

我有下表:

'committee' table

commname    profname
========================
commA       bill
commA       jack
commA       piper
commB       bill
commB       piper

我正在寻找派"成员所在的每个委员会中的教授 (答案应该是吹笛者和账单):

and I am trying to find the professors who are in every committee that 'piper' is in (answer should be piper and bill):

我有以下SQL除法查询，但是它是错误的，我无法弄清楚问题出在哪里(不退还账单，只是吹笛者):

I have the following SQL division query but it's wrong and I can't figure out where the problem is (doesn't return bill, just piper):

select b.profname
from committee b
where not exists 

(select commname
from committee a
where profname = 'piper' and not exists 

(select commname
from committee
where a.profname=b.profname ))

有人可以帮我吗? 谢谢，

Can somebody help me with this one? Thanks,

推荐答案

您最里面的select在其where子句中没有使用任何东西，因此它总是在为piper找到一些东西.试试

Your innermost select isn't using anything from itself in its where clause, so it's always finding something for piper. Try

select distinct b.profname from committee b
where not exists (
    select commname from committee a
    where a.profname = 'piper' and not exists  (
        select commname from committee c
        where c.profname=b.profname and c.commname=a.commname
    )
);

这篇关于在MySQL中使用“不存在"的SQL Division的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！