Help building a SQL query from multiple tables

Question

Given the following tables, how might I build a SQL query that includes a list of all the items from the "items" table, and a column for each color from the "colors" table that, for each item listed, indicates what colors the item has a relationship with.

If that is unclear at all, please let me know what additional information will help clarify. The table information and desired SQL result are below:

items table:

id | item_name
1  | 'item 1'
2  | 'item 2'
3  | 'item 3'

colors table:

id | color_name
1  | 'red'
2  | 'blue'
3  | 'green'

item_color table:

item_id | color_id
1       | 1
1       | 3
2       | 2
2       | 3
3       | 2

Desired SQL query result:

item_name | red | blue | green
'item 1'  |  1  | null |   1
'item 2'  | null|   1  |   1
'item 3'  | null|   1  | null

Thanks, Colin

Answer 1

Are you on oracle 11g?

This seems to be an ideal use for the new pivot feature in 11g

Answer 2

Use:

SELECT item_name,
       MAX(red) 'red',
       MAX(blue) 'blue',
       MAX(green) 'green'
  FROM (SELECT t.item_name,
         CASE
           WHEN c.color_name = 'red' THEN
             1
           ELSE
             NULL
         END 'red',
         CASE
           WHEN c.color_name = 'blue' THEN
             1
           ELSE
             NULL
         END 'blue',
         CASE
           WHEN c.color_name = 'green' THEN
             1
           ELSE
             NULL
         END 'green'       
    FROM ITEMS t
    JOIN ITEM_COLOR ic ON ic.item_id = t.item_id
    JOIN COLORS c ON c.id = ic.color_id)
GROUP BY item_name

Change MAX to COUNT if you want the total # of red/blue/green associated to an item.

Alternate using Subquery Factoring:

WITH icolors AS (
   SELECT t.item_name,
          CASE
           WHEN c.color_name = 'red' THEN
             1
           ELSE
             NULL
         END 'red',
     CASE
       WHEN c.color_name = 'blue' THEN
         1
       ELSE
         NULL
     END 'blue',
     CASE
       WHEN c.color_name = 'green' THEN
         1
       ELSE
             NULL
     END 'green'       
    FROM ITEMS t
    JOIN ITEM_COLOR ic ON ic.item_id = t.item_id
    JOIN COLORS c ON c.id = ic.color_id)
  SELECT t.item_name,
         MAX(t.red) 'red',
         MAX(t.blue) 'blue',
         MAX(t.green) 'green'
    FROM icolors t
GROUP BY t.item_name

Answer 3

If you know all the possible colours in advance, you can do it messily but effectively. If you don't know all the possible colours in advance, it is very much harder - you have to run some queries to find out which columns will appear in the result table, and then craft the SQL to create those columns (dynamic SQL).

So, let's assume you know the columns in the result table:

SELECT i.item_name, r.red, b.blue, g.green
  FROM items i
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS red
          FROM item_color
         WHERE color_id = 1
         GROUP BY item_name) AS r
       ON i.item_name = r.item_name
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS green
          FROM item_color
         WHERE color_id = 3
         GROUP BY item_name) AS g
       ON i.item_name = g.item_name
       LEFT JOIN
       (SELECT item_name, COUNT(*) AS blue
          FROM item_color
         WHERE color_id = 2
         GROUP BY item_name) AS b
       ON i.item_name = b.item_name

Note that in this formulation, I've used the data from the colours table in building the query. And alternative form would build the sub-queries as (inner) joins to the colours table, using the colour name instead of the code in the WHERE clauses.