How to join two tables with three pairs-CodePudding

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?

CodePudding user response：

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