MySQL: How can I condense this verbose query?

Question

Here is my schema:

Suppliers(sid: integer, sname: string, address string)

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

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

Primary keys 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;

The subqueries on either side of the UNION statement are hideously repeated. In imperative programming, this would be a good place to make a function, taking the color as a parameter. What is the equivalent of this in MySQL?

I have heard of "views" but I think that might be overkill for this case.

Answer 1

This:

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
                        )
                )
        );

or this:

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

Pushed the color condition into the WHERE clause of subselects, adding the color to the grouping and added DISTINCT to support identical behavior of 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 

I don't know that every DBMS out there would be able to fully optimize a generic version of this by pushing the "color" condition into the subselects, like:

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])