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. 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

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

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.

Answer

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.

Advertisement

Answer