mysql - 选择属于一个主机的所有项目

Question

我有以下关系

Supplies            
sid     1       2       3       4
sname   Jason   David   John    Peter
address 1a      2b      3c      4d


Parts                       
pid     10      20      30      40      50
pname   Head    Body    Hand    Leg     Arm
color   red     blue    green   white   red


Catalog             
sid     1   1   2   2   3   4   1   1   4   4   1
pid     10  20  20  30  30  40  30  40  10  50  50
cost    100 200 150 150 130 125 50  180 123 126 120

我想选择供应每个红色或绿色零件的供应商的 sid。我认为这意味着不存在非他提供的绿色或红色部分。

所以我做了以下查询，但它返回 null，我认为它应该返回 sid 为 1。

SELECT S.sid
FROM Suppliers S
WHERE NOT 
EXISTS (

SELECT P.pid
FROM Parts P
WHERE P.color = 'red' OR P.color = 'green'
AND NOT 
EXISTS (

SELECT C.pid
FROM Catalog C
WHERE C.pid = P.pid
AND C.sid = S.sid
)
)

我该如何解决？提前致谢。

Answer 1

如果你想要提供红色和绿色两种颜色零件的供应商，那么你可以使用类似这样的查询:

select s.sid
from supplies s
inner join catalog c
  on s.sid = c.sid
inner join parts p
  on c.pid = p.pid
where p.color in ('red', 'green')
group by s.sid
having count(distinct p.color) = 2;

参见 SQL Fiddle with Demo

或者@ypercube 指出的更好的方法:

select s.sid
from supplies s
left join catalog c
  on s.sid = c.sid
left join parts p
  on c.pid = p.pid
 and p.color in ('red', 'green')
group by s.sid
having count(distinct p.pid) = (select count(*)
                                from parts 
                                where color in ('red', 'green'));

参见 SQL Fiddle with Demo