title: MSSQL: left join multiple columns in three tables
body: <p>I have three tables in MS SQL Server:</p>
  <pre><code>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
  </code></pre>
  <p>The Intervals table has three rows: [1,'Monthly'], [2,'BiWeekly'] and [3,'Weekly']</p>
  <p>I need a query that has this result set:</p>
  <pre><code>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   
  </code></pre>
  <p>Leaving aside the firstname+lastname column for now, my first try used two left joins successfully:</p>
  <pre><code>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
  </code></pre>
  <p>and returned this:</p>
  <pre><code>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
  </code></pre>
  <p>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.</p>
  <p>I tried multiple left joins for the same table (tenants), and ORing conditions on one join; they both seemed intuitively wrong.</p>
  <pre><code>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  
  </code></pre>
  <p>but both of these returned the same lastname in two columns.</p>