mysql 范围 不存在的,在MySQL中使用'不存在'的SQL分区

I have the following table:

'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):

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,

解决方案

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

)

);