MySQL: How can I condense this detailed query?

Question

Here is my diagram:

Providers ( sid: integer , sname: string, address bar)

Parts ( pid: integer , pname: string, color: string)

Directory ( sid: integer , pid: integer, cost: real)

Primary keys are in bold.

Here is the MySQL query I'm working with:

-- Find the sids of suppliers who supply every red part or supply every green part.
-- this isn't DRY
-- not tested
SELECT Suppliers.sid
FROM Suppliers 
JOIN (SELECT sid, COUNT(Parts.pid) AS partsPerSupplier
    FROM Catalog
    JOIN Parts on Catalog.pid = Parts.pid
    WHERE Parts.color = "red"
    GROUP BY sid)
AS partCounts ON Suppliers.sid = partCounts.sid
JOIN (SELECT COUNT(pid) AS totalParts 
    FROM Parts
    WHERE color = "red"
) AS totalPartsTable ON totalPartsTable.totalParts = partCounts.partsPerSupplier
UNION
SELECT Suppliers.sid
FROM Suppliers 
JOIN (SELECT sid, COUNT(Parts.pid) AS partsPerSupplier
    FROM Catalog
    JOIN Parts on Catalog.pid = Parts.pid
    WHERE Parts.color = "green"
    GROUP BY sid)
AS partCounts ON Suppliers.sid = partCounts.sid
JOIN (SELECT COUNT(pid) AS totalParts 
    FROM Parts
    WHERE color = "green"
) AS totalPartsTable ON totalPartsTable.totalParts = partCounts.partsPerSupplier;

Subqueries from both sides of the operator are UNIONdisgustingly repeated. In imperative programming, this would be a good place to create a function, taking color as a parameter. What is equivalent to this in MySQL?

I have heard of “views,” but I think that may be redundant for this case.

Answer 1

:

SELECT  *
FROM    suppliers s
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    (
                SELECT  'red' AS color
                UNION ALL
                SELECT  'green'
                ) ci
        WHERE   color NOT IN
                (
                SELECT  color
                FROM    parts p
                WHERE   p.pid NOT IN
                        (
                        SELECT  pid
                        FROM    catalog c
                        WHERE   c.sid = s.sid
                        )
                )
        );

:

SELECT  c.sid
FROM    (
        SELECT  color, COUNT(*) AS total
        FROM    parts
        WHERE   color IN ('red', 'green')
        GROUP BY
                color
        ) t
JOIN    parts p
ON      p.color = t.color
JOIN    catalog c
ON      c.pid = p.pid
GROUP BY
        sid, color
HAVING  COUNT(*) = total;

Answer 2

WHERE , DISTINCT UNION:

SELECT distinct Suppliers.sid 
  FROM Suppliers  
       inner JOIN (SELECT sid, color, COUNT(Parts.pid) AS partsPerSupplier 
                     FROM Catalog 
                    inner JOIN Parts on Catalog.pid = Parts.pid 
                    WHERE Parts.color in ("red" , "green")
                    GROUP BY sid, color) AS partCounts 
          ON Suppliers.sid = partCounts.sid 
       inner JOIN (SELECT color, COUNT(pid) AS totalParts  
                     FROM Parts
                    WHERE Parts.color in ("red", "green")
                    GROUP BY color) AS totalPartsTable 
          ON partCounts.color = totalPartsTable.color 
         AND totalPartsTable.totalParts = partCounts.partsPerSupplier 

, , "" , :

SELECT distinct Suppliers.sid 
  FROM Suppliers  
       inner JOIN (SELECT sid, color, COUNT(Parts.pid) AS partsPerSupplier 
                     FROM Catalog 
                    inner JOIN Parts on Catalog.pid = Parts.pid 
                    GROUP BY sid, color) AS partCounts 
          ON Suppliers.sid = partCounts.sid 
       inner JOIN (SELECT color, COUNT(pid) AS totalParts  
                     FROM Parts
                    GROUP BY color) AS totalPartsTable 
          ON partCounts.color = totalPartsTable.color 
         AND totalPartsTable.totalParts = partCounts.partsPerSupplier 
  WHERE color in ([some list of colors])