Join a single column of value labels to multiple columns of values in another table in SQL

user_11842269 0
Viewed 56

I have two tables: Preferences, which shows students' meal preferences, and Key, which is a key to understanding what each of the values are for each meal item.


Preferences:

| Student_ID | Student_Name | Meat | Vegetable | Drink | Dessert |
|------------|--------------|------|-----------|-------|---------|
| 1          | Jeff         | 3    | 1         | 4     | 1       |
| 2          | Andrea       | 1    | 1         | 3     | 1       |
| 3          | Allison      | 3    | 2         | 7     | 1       |
| 4          | Randy        | 1    | 1         | 4     | 2       |
| 5          | Carl         | 2    | 5         | 2     | 2       |
| 6          | Bobby        | 1    | 6         | 7     | 2       |
| 7          | Julie        | 3    | 5         | 2     | 1       |
| 8          | Anna         | 1    | 6         | 7     | 2       |
| 9          | Carlos       | 1    | 6         | 2     | 2       |
| 10         | Roger        | 2    | 4         | 2     | 1       |
| 11         | Pierre       | 1    | 2         | 1     | 1       |
| 12         | Troy         | 2    | 3         | 3     | 1       |
| 13         | David        | 3    | 6         | 6     | 2       |
| 14         | Michaela     | 1    | 4         | 5     | 2       |
| 15         | Rose         | 1    | 4         | 6     | 1       |
| 16         | Anita        | 3    | 6         | 6     | 2       |
| 17         | Connor       | 3    | 3         | 3     | 1       |
| 18         | Eddie        | 1    | 2         | 7     | 1       |
| 19         | Karen        | 3    | 5         | 5     | 2       |
| 20         | Rachel       | 3    | 2         | 2     | 1       |
|            |              |      |           |       |         |

Key:

| Item_Type | Item     | Value |
|-----------|----------|-------|
| Meat      | Chicken  | 1     |
| Meat      | Beef     | 2     |
| Meat      | Fish     | 3     |
| Vegetable | Carrots  | 1     |
| Vegetable | Peas     | 2     |
| Vegetable | Corn     | 3     |
| Vegetable | Broccoli | 4     |
| Vegetable | Zucchini | 5     |
| Vegetable | Eggplant | 6     |
| Drink     | Water    | 1     |
| Drink     | Milk     | 2     |
| Drink     | Juice    | 3     |
| Drink     | Cola     | 4     |
| Drink     | Lemonade | 5     |
| Drink     | Tea      | 6     |
| Drink     | Punch    | 7     |
| Dessert   | Cake     | 1     |
| Dessert   | Pie      | 2     |

I am trying to figure out the most efficient way to assign value labels to each column of meal item values in Preferences from the single Item column from Key. In other words, I want to JOIN the two tables to create the following table:

| Student_ID | Student_Name | Meat    | Vegetable | Drink    | Dessert |
|------------|--------------|---------|-----------|----------|---------|
| 1          | Jeff         | Fish    | Carrots   | Cola     | Cake    |
| 2          | Andrea       | Chicken | Carrots   | Juice    | Cake    |
| 3          | Allison      | Fish    | Peas      | Punch    | Cake    |
| 4          | Randy        | Chicken | Carrots   | Cola     | Pie     |
| 5          | Carl         | Beef    | Zucchini  | Milk     | Pie     |
| 6          | Bobby        | Chicken | Eggplant  | Punch    | Pie     |
| 7          | Julie        | Fish    | Zucchini  | Milk     | Cake    |
| 8          | Anna         | Chicken | Eggplant  | Punch    | Pie     |
| 9          | Carlos       | Chicken | Eggplant  | Milk     | Pie     |
| 10         | Roger        | Beef    | Broccoli  | Milk     | Cake    |
| 11         | Pierre       | Chicken | Peas      | Water    | Cake    |
| 12         | Troy         | Beef    | Corn      | Juice    | Cake    |
| 13         | David        | Fish    | Eggplant  | Tea      | Pie     |
| 14         | Michaela     | Chicken | Broccoli  | Lemonade | Pie     |
| 15         | Rose         | Chicken | Broccoli  | Tea      | Cake    |
| 16         | Anita        | Fish    | Eggplant  | Tea      | Pie     |
| 17         | Connor       | Fish    | Corn      | Juice    | Cake    |
| 18         | Eddie        | Chicken | Peas      | Punch    | Cake    |
| 19         | Karen        | Fish    | Zucchini  | Lemonade | Pie     |
| 20         | Rachel       | Fish    | Peas      | Milk     | Cake    |
|            |              |         |           |          |         |

Where each meal item value for each student in Preferences is matched to its appropriate label from the single Item column in Key.

join sql
4 Answers

Here's a solution with a single join.
It was implemented in MySQL, but with very slight changes it can be implemented in any other DB.

select    Preferences.Student_ID
         ,Preferences.Student_Name
         ,max(case `Key`.Item_Type when 'Meat'      then `Key`.item end) as Meat
         ,max(case `Key`.Item_Type when 'Vegetable' then `Key`.item end) as Vegetable
         ,max(case `Key`.Item_Type when 'Drink'     then `Key`.item end) as Drink
         ,max(case `Key`.Item_Type when 'Dessert'   then `Key`.item end) as Dessert
from Preferences join `Key`on 
         `Key`.Item_Type = 'Meat'      and `Key`.value = Preferences.Meat      
      or `Key`.Item_Type = 'Vegetable' and `Key`.value = Preferences.Vegetable 
      or `Key`.Item_Type = 'Drink'     and `Key`.value = Preferences.drink     
      or `Key`.Item_Type = 'Dessert'   and `Key`.value = Preferences.Dessert
group by Preferences.Student_ID, Preferences.Student_Name
order by Preferences.Student_ID
Student_ID Student_Name Meat Vegetable Drink Dessert
1 Jeff Fish Carrots Cola Cake
2 Andrea Chicken Carrots Juice Cake
3 Allison Fish Peas Punch Cake
4 Randy Chicken Carrots Cola Pie
5 Carl Beef Zucchini Milk Pie
6 Bobby Chicken Eggplant Punch Pie
7 Julie Fish Zucchini Milk Cake
8 Anna Chicken Eggplant Punch Pie
9 Carlos Chicken Eggplant Milk Pie
10 Roger Beef Broccoli Milk Cake
11 Pierre Chicken Peas Water Cake
12 Troy Beef Corn Juice Cake
13 David Fish Eggplant Tea Pie
14 Michaela Chicken Broccoli Lemonade Pie
15 Rose Chicken Broccoli Tea Cake
16 Anita Fish Eggplant Tea Pie
17 Connor Fish Corn Juice Cake
18 Eddie Chicken Peas Punch Cake
19 Karen Fish Zucchini Lemonade Pie
20 Rachel Fish Peas Milk Cake

Fiddle

user_191745700

Here is a full, very lean SQL Server solution based on unpivot & pivot.
Please note that subquery is needed only for the pivot statement since any column that isn't part of that statement becomes part of an implicit group by.

select  *
from   (select  item
               ,food_type
               ,Student_ID
               ,Student_Name
        from    Preferences as p
        unpivot (food for food_type in (Meat,Vegetable,Drink,Dessert)) as u 
        join food on food.value =  u.food and  food.Item_Type = u.food_type) as pu
        pivot (max(item) for food_type in(Meat, Vegetable, Drink, Dessert)) as p
order by Student_ID
Student_ID Student_Name Meat Vegetable Drink Dessert
1 Jeff Fish Carrots Cola Cake
2 Andrea Chicken Carrots Juice Cake
3 Allison Fish Peas Punch Cake
4 Randy Chicken Carrots Cola Pie
5 Carl Beef Zucchini Milk Pie
6 Bobby Chicken Eggplant Punch Pie
7 Julie Fish Zucchini Milk Cake
8 Anna Chicken Eggplant Punch Pie
9 Carlos Chicken Eggplant Milk Pie
10 Roger Beef Broccoli Milk Cake
11 Pierre Chicken Peas Water Cake
12 Troy Beef Corn Juice Cake
13 David Fish Eggplant Tea Pie
14 Michaela Chicken Broccoli Lemonade Pie
15 Rose Chicken Broccoli Tea Cake
16 Anita Fish Eggplant Tea Pie
17 Connor Fish Corn Juice Cake
18 Eddie Chicken Peas Punch Cake
19 Karen Fish Zucchini Lemonade Pie
20 Rachel Fish Peas Milk Cake

Fiddle

user_191745700

I'm not sure about the most efficient way as you mentioned, but you can perform an inner join four times as the following:

Select P.Student_ID, P.Student_Name, 
K1.item as Meat, K2.item as Vegetable,K3.item as Drink, K4.item as Dessert
from Preferences P 
inner Join Key_ K1 On (P.Meat=K1.Value_ and K1.Item_Type='Meat')
inner Join Key_ K2 On (P.Vegetable=K2.Value_ and K2.Item_Type='Vegetable')
inner Join Key_ K3 On (P.Drink=K3.Value_ and K3.Item_Type='Drink')
inner Join Key_ K4 On (P.Dessert=K4.Value_ and K4.Item_Type='Dessert')
order by P.Student_ID

See demo from db-fiddle.

user_127059120

Another way is to do two operations, first a unpivot - and then an inner join. Not sure if you'd want to pivot the result again, but if you did I guess that would be a third operation.

WITH CTE_UNPIVOT AS (
SELECT * FROM Preferences
UNPIVOT( VALUE for ITEM_TYPE in ( Meat, Veg, Drink, Dessert ))
),
CTE_MAPPED AS (
SELECT * FROM CTE_UNPIVOT
INNER JOIN Key
ON CTE_UNPIVOT.ITEM_TYPE = Key.ITEM_TYPE
)
SELECT * FROM CTE_MAPPED;
user_22120590
Related