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.
No comments:
Post a Comment