How to join two tables with three pairs

Lukasz Babel 0
Viewed 18

I have two tables that I want to combine (join) in SQL.

Table 1 Persons:

Person _Id First_name Last_name
135790 John Smith
246801 Lucas Williams
054953 George Johnson
460235 Adam White

Table 2 Loans:

Borrower_Id resident1_id resident2_id
135790 246801 054953
460235 054953 135790
054953 246801 135790

The expected result:

Borrower_FN Borrower_LN resident1_FN resident1_LN resident2_FN resident1_LN
John Smith Lucas Williams George Johnson
Adam White George Johnson John Smith
George Johnson Lucas Williams John Smith

How can I join it?

join sql
1 Answers

This is a way you can achieve the result:

select p.First_name as Borrower_FN, p.Last_name as Borrower_LN,
 p2.First_name as resident1_FN, p2.Last_name as resident1_LN,
 p3.First_name as resident2_FN, p3.Last_name as resident2_LN
     from Loans l
     inner join Persons p on p.Person_id = l.Borrower_id
     inner join Persons p2 on p2.Person_id = l.resident1_id
     inner join Persons p3 on p3.Person_id = l.resident2_id
LMigMa490
Related