I have 2 tables 1 and 2, I will like to query to combine the result as below
Table_1
| Row # | ID | Age | Color |
|---|---|---|---|
| 1 | 1234 | 15 | Red |
| 2 | 1234 | 15 | Yellow |
| 3 | 1235 | 16 | Green |
| 4 | 1235 | 16 | Yellow |
| 5 | 1235 | 16 | White |
| 6 | 1235 | 16 | Green |
| 7 | 1236 | 21 | Black |
| 8 | 1236 | 21 | Pink |
Table_2
| Row # | ID | Age | Fruit |
|---|---|---|---|
| 1 | 1234 | 15 | Orange |
| 2 | 1234 | 15 | Apple |
| 3 | 1235 | 16 | Banana |
| 4 | 1235 | 16 | Peach |
| 5 | 1236 | 21 | Banana |
| 6 | 1236 | 21 | Orange |
| 7 | 1236 | 21 | Kiwifruit |
Query result should be
| Row # | ID | Age | Color | Fruit |
|---|---|---|---|---|
| 1 | 1234 | 15 | Red | Orange |
| 2 | 1234 | 15 | Yellow | Apple |
| 3 | 1235 | 16 | Green | Banana |
| 4 | 1235 | 16 | Yellow | Peach |
| 5 | 1235 | 16 | White | Null |
| 6 | 1235 | 16 | Green | Null |
| 7 | 1236 | 21 | Black | Banana |
| 8 | 1236 | 21 | Pink | Orange |
| 9 | 1236 | 21 | Null | Kiwifruit |
This looks like very easy, but I could not get it. I am using sql server 2019 database and ssms 19. I tried,
Select t1.*, t2.Fruit from Table_1 t1
left join Table_2 t2 on t1.ID = t2.ID