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.
CodePudding user response：
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.
CodePudding user response：
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;