MSSQL: left join multiple columns in three tables-CodePudding

I have three tables in MS SQL Server:

Tenants:
TenantID int,
LastName varchar(50),
FirstName varchar(50)

Intervals:
Value int,
Name varchar(50)

Leases:
LeaseID int,
Interval int,
StartDate date,
EndDate date,
Tenant1 int,
Tenant2 int,
Tenant3 int,
Tenant4 int

The Intervals table has three rows: [1,'Monthly'], [2,'BiWeekly'] and [3,'Weekly']

I need a query that has this result set:

LID Type      Start         End      Tenant1       Tenant2        Tenant3       Tenant4
21  Monthly  12/15/2019  12/15/2020  Sean Murphy   Audrey Moore   Randy Davis
32  Weekly   06/01/2018              Pete Higgins  Kathy Higgins  
35  Monthly  08/01/2019  10/31/2020  Andy Stacke   Valerie Stacke
44  Monthly  01/01/2021              Pete O'Toole  Martha White

Leaving aside the firstname lastname column for now, my first try used two left joins successfully:

select L.LeaseID, I.Name as Type, L.StartDate, L.EndDate, T.LastName as Tenant1 
from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Tenant1

and returned this:

LID Type      Start         End      Tenant1 
21  Monthly  12/15/2019  12/15/2020  Murphy  
32  Weekly   06/01/2018              Higgins
35  Monthly  08/01/2019  10/31/2020  Stacke
44  Monthly  01/01/2021              O'Toole

A good start, but I can't seem to get up to 4 unique names from the Tenants table, and I would like to combine (firstname lastname) in the tenant columns.

I tried multiple left joins for the same table (tenants), and ORing conditions on one join; they both seemed intuitively wrong.

select L.lid,L.ruid, I.Name, T.LastName as Tenant1, T.LastName as Tenant2 from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Ten1
left join Tenants T on T.TID = L.Ten2

select L.lid,L.ruid, I.Name, T.LastName as Tenant1, T.Lastname as Tenant2 from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T on T.TID = L.Ten1 or T.TID = L.Ten2

but both of these returned the same lastname in two columns.

CodePudding user response：

Working without a net here but I believe you want something like this:

select 
    L.LeaseID AS 'LID', 
    I.Name AS 'Type', 
    L.StartDate AS 'Start', 
    L.EndDate AS 'End', 
    CONCAT(T1.FirstName, ' ', T1.LastName) AS 'Tenant1', 
    CONCAT(T2.FirstName, ' ', T2.LastName) AS 'Tenant2', 
    CONCAT(T3.FirstName, ' ', T3.LastName) AS 'Tenant3', 
    CONCAT(T4.FirstName, ' ', T4.LastName) AS 'Tenant4'
from Leases L
left join Intervals I ON L.Interval = I.Value
left join Tenants T1 ON L.Tenant1 = T1.TenantID
left join Tenants T2 ON L.Tenant2 = T2.TenantID
left join Tenants T3 ON L.Tenant3 = T3.TenantID
left join Tenants T4 ON L.Tenant4 = T4.TenantID

CodePudding user response：

Try this

select
  L.lid
  ,L.ruid
  , I.Name
  , T1.LastName as Tenant1
  , T2.LastName as Tenant2 
from Leases L 
left join Intervaltypes I on I.value=L.Interval 
left join Tenants T1 on T1.TID = L.Ten1
left join Tenants T2 on T2.TID = L.Ten2