SQL query that returns values based on lookups of id in another table - with nullable values

Question

I found this question which is very similar, but mine goes a little further. I'm going to use his example but expand it a little more. Basically I'm adding another column for

I have 2 tables:

  dbo.Events
    ID               StartingLocation            EndingLocation
    1                     1                           null
    2                     2                           1

  dbo.EventsLocation
    ID    LocationName
    1           Room 1
    2           Room 2

What I want to do is write a query that will give me a result that looks like this:

ID     StartingLocation   EndingLocation
1           Room 1            null
2           Room 2            Room 1

I know I need to do some type of (inner?) join. But I'm getting stuck on the fact that I need to insert the data into two columns, and the fact that a value in EndingLocation can be null.

What I've tried:

SELECT Events.id AS EventID, EventsLocation.LocationName AS StartLocation, EventsLocation.LocationName AS EndLocation
FROM Events
INNER JOIN EventsLocation
on Events.StartingLocation=EventsLocation.id 
AND Events.EndingLocation=EventsLocation.id

but this gives me no results. If I chop off the AND condition, I get the following table that just repeats the StartingLocation twice.

EventID    StartLocation     EndLocation
1          Room 1            Room 1
2          Room 2            Room 2

Can anyone help me get on the right track?

Answer 1

You want two joins. Just in case one of the columns is NULL, I recommend LEFT JOINs:

SELECT e.id AS EventID, else.LocationName AS StartLocation, ele.LocationName AS EndLocation
FROM Events e LEFT JOIN
     EventsLocation els
     ON e.StartingLocation = els.id LEFT JOIN
     EventsLocation ele
     ON e.EndingLocation = ele.id;