Multiple Join

Transact-SQL
1

Hi all,
I need to join the following two tables A and B, to have table C:

A:

i j k
1 John Red
2 Mike Yellow
3 Ted Orange

B:

x y z
1 2 Toronto
2 3 New York
3 1 Chicago

C:

i j k l m
1 John Red Toronto Chicago
2 Mike Yellow New York Toronto
3 Ted Orange Chicago New York

Can you please help me what would be the SQL code for this?
Thanks

2

Create table #A ( i int, j varchar(255),k varchar(255))

insert into #A
values
(1,'John','Red')
,(2,'Mike','Yellow')
,(3,'Ted','Orange')

Create table #B (x int, y int,z varchar(255))

insert into #B
values
(1,2,'Toronto')
,(2,3,'New York')
,(3,1,'Chicago')

Select * from #A

Select * from #B

Select
a.*
,b.z as l
,c.z as m
from #A a
Left join #B b on a.i = b.x
Left join #B c on a.i = c.y

1 Like