Keeping SQL Dry

Question

Here is a MySQL query I'm running:

-- get the sid of every supplier who does not supply both a red and green part
SELECT Suppliers.sid, Parts.color
FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid
WHERE Suppliers.sid NOT IN (
    SELECT Suppliers.sid
    FROM Suppliers
    JOIN Catalog ON Catalog.sid = Suppliers.sid
    JOIN Parts ON Parts.pid = Catalog.pid
    WHERE Parts.color IN ('red', 'green')
    GROUP BY Suppliers.sid
    HAVING COUNT(DISTINCT Parts.color) = 2
)
ORDER BY Suppliers.sid DESC;

As you can see, this is repeated twice:

FROM Suppliers
JOIN Catalog ON Catalog.sid = Suppliers.sid
JOIN Parts ON Parts.pid = Catalog.pid

What can I do to only declare this once, and reference it twice? Or is this a sign that my whole query is flawed somehow?

The problem is exacerbated when I have 15 different queries I'm running with those same three lines.

Answer 1

If you are going to continue to use a relationship of tables together throughout your system, then what you want to look into is called a view.

Answer 2

A more ANSI solution I assume would work in MySQL:

SELECT X.sid, X.color 
  FROM (SELECT Catalog.sid, parts.color, 
               count(distinct parts.color) as distinct_color_count
          from Catalog 
               inner jOIN Parts 
                  ON Parts.pid = Catalog.pid 
         where parts.color in ("red", "green")
         group by catalog.sid, parts.color)
       ) x
 where x.distinct_color_count = 2
 order by x.sid desc

You don't pull anything out of Suppliers but sid, and that must exist in Catalog, so use Catalog.sid instead.

Nest the select statement so you get the distinct_color_count so you can filter for it in a following step. If you try this:

SELECT Catalog.sid, parts.color, 
       count(distinct parts.color) 
  from Catalog 
       inner join Parts 
          ON Parts.pid = Catalog.pid 
 where parts.color in ("red", "green")
having count(distinct parts.color) = 2
 group by catalog.sid, parts.color
 order by catalog.sid ;

it won't work, because each row will have only one distinct color in it.