SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table1 (
id varchar(10),
name varchar(10));
  
INSERT INTO table1 VALUES ('1', 'John'), ('1', 'Merry'), ('2', 'Ted'), ('3', 'Jack'), ('3', 'Zed');

SELECT *
FROM table1;


id
name


1
John

1
Merry

2
Ted

3
Jack

3
Zed


CREATE TABLE table2 (
id varchar(10),
name varchar(10));
  
INSERT INTO table2 VALUES ('1234', 'John'), ('1234', 'Peter'), ('1236', 'Gerry'), ('1237', 'Grey'), ('1237', 'Zed');
SELECT *
FROM table2;


id
name


1234
John

1234
Peter

1236
Gerry

1237
Grey

1237
Zed


SELECT CAST(A.id AS INT) AS id,A.name AS name, A.id AS c
FROM table1 AS A
UNION 
SELECT CAST(E.id AS INT) AS id,E.name AS name,ISNULL(F.c,E.id) AS c
FROM table2 E
LEFT JOIN (
SELECT D.id,D.name, B.id AS c
FROM table1 AS B
INNER JOIN table2 AS D ON D.name=B.name
) AS F ON F.id=E.id
ORDER BY id,name


id
name
c


1
John
1

1
Merry
1

2
Ted
2

3
Jack
3

3
Zed
3

1234
John
1

1234
Peter
1

1236
Gerry
1236

1237
Grey
3

1237
Zed
3

id	name	c
1	John	1
1	Merry	1
2	Ted	2
3	Jack	3
3	Zed	3
1234	John	1
1234	Peter	1
1236	Gerry	1236
1237	Grey	3
1237	Zed	3