1. SELECTFROM Parts pINNER JOIN (SELECT pid FROM Catalog GROUP BY pid HAVING COUNT(DISTINCT sid) >=2)sON s.pid = p.pid
2.SELECT s.*FROM Suppliers sCROSS JOIN (SELECT pid FROM Parts WHERE Color='Red')pLEFT JOIN Catalog cON c.sid = s.sidAND c.pid = p.idGROUP BY s.sidHAVING SUM(CASE WHEN c.sid IS NULL THEN 1 ELSE 0 END)=0
3.SELECT s.*FROM Suppliers sCROSS JOIN (SELECT pid FROM Parts WHERE Color IN ('Red','Green'))pLEFT JOIN Catalog cON c.sid = s.sidAND c.pid = p.idGROUP BY s.sidHAVING COUNT(DISTINCT CASE WHEN c.sid IS NULL THEN p.Color ELSE 0 END)<=1
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/