Get values of columns A of table 1 and values of column B of table 2 with same ID

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 SSMS19. I tried,

select t1.*, t2.Fruit
from Table_1 t1
left join Table_2 t2 on t1.ID = t2.ID

Solution

Based on your data set you need to generate your own rank within each ID/Age group to allow you to join between the tables. You then need to use FULL JOIN because you want data from either table. The following query gives your desired output:

DECLARE @T1 TABLE(RowID INT, ID INT, Age INT, Color VARCHAR(20));
INSERT  @T1(RowID, ID, Age, Color)
VALUES
    (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');

DECLARE @T2 TABLE(RowID INT, ID INT, Age INT, Fruit VARCHAR(20));
INSERT  @T2(RowID, ID, Age, Fruit)
VALUES
    (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');

SELECT  RowID = ROW_NUMBER() OVER(ORDER BY ISNULL(t1.ID,t2.ID)),
        ID = ISNULL(t1.ID,t2.ID),
        Age = ISNULL(t1.Age,t2.Age),
        t1.Color,
        t2.Fruit
FROM    (   SELECT  *, RowNumber=ROW_NUMBER() OVER(PARTITION BY ID,t.Age ORDER BY t.RowID)
            FROM    @T1 AS t
        ) AS t1
        FULL JOIN 
        (   SELECT  *, RowNumber=ROW_NUMBER() OVER(PARTITION BY ID,t.Age ORDER BY t.RowID)
            FROM    @T2 AS t
        ) AS t2
            ON t2.ID = t1.ID
            AND t2.Age = t1.Age
            AND t2.RowNumber = t1.RowNumber