Referencing a table twice

[GWTNewbie]

Hello,

I am trying to find a way to join a table but I need to "join it twice". Let me explain. I have a table (TrackLocation) that is tracking changes in a location table(Location):

TrackLocation:
ID / OldLocation / NewLocation / Timestamp

Location:
ID / Prefix / StreetName / StreetType / Suffix

Both "OldLocation" and "NewLocation" reference the Location.ID field. How can I write a join statement such that I get the street name for both OldLocation and NewLocation?

SELECT T.ID, L.StreetName, L.StreetName, T.Timestamp FROM TrackLocation T
INNER JOIN Location L ON
L.ID = T.OldLocation AND L.ID = T.NewLocation

Yes, I know the above SQL statement is wrong. What is the correct way to write it?

[Bob Barrows]

Post by GWTNewbie
Hello,
I am trying to find a way to join a table but I need to "join it
twice". Let me explain. I have a table (TrackLocation) that is
ID / OldLocation / NewLocation / Timestamp
ID / Prefix / StreetName / StreetType / Suffix
Both "OldLocation" and "NewLocation" reference the Location.ID field.
How can I write a join statement such that I get the street name for
both OldLocation and NewLocation?
SELECT T.ID, L.StreetName, L.StreetName, T.Timestamp FROM
TrackLocation T
INNER JOIN Location L ON
L.ID = T.OldLocation AND L.ID = T.NewLocation
Yes, I know the above SQL statement is wrong. What is the correct way to write it?

You need to use table aliases, with a different alias for each join:

SELECT T.ID
, old.StreetName As StreetNameOld
, new.StreetName As SteetNameNew
, T.Timestamp
FROM
TrackLocation As T
INNER JOIN Location As old ON L.ID = old.OldLocation
INNER JOIN Location As new ON L.ID = new.NewLocation