mysql - 我正在尝试执行 mysql 查询以获取只供应红色部分的供应商？

Question

这里是mysql查询

select distinct C.sid
 from Catalog C  
where not exists 
( select * from Parts P where P.pid = C.pid and P.color != 'red');

但它返回供应红色零件的供应商而不仅仅是红色零件？以及如何改变它的想法

我有3张 table

Suppliers(sid: integer, sname: string, address: string) 
Parts(pid: integer, pname: string, color: string) 
Catalog(sid: integer, pid: integer, cost: real) 

任何帮助将不胜感激，谢谢

Answer 1

select
    sid
from
    suppliers s
where
    exists (
        select 
            'x'
        from
            catalog c
                inner join
            parts p
                on c.pid = p.pid
        where
            s.sid = c.sid and
            p.color = 'red'
    ) and
    not exists (
        select
            'x'
        from
            catalog c
                inner join
            parts p
                on c.pid = p.pid
        where
            s.sid = c.sid and
            p.color != 'red'
    )