Saturday 31 October 2015

How to perform a join on two foreign keys from the same table

Let me describe the scenario in little more details.

Lets say i have a Taxi and Location Table.
Taxi table has 2 columns "startLocationId" and "endLocationId". Both these columns are foreign key to Location table.

Now while printing the results of Taxi Table, I want to print the Location Name and not just Location Id.

Here is the query.

select t.TaxiNo, l1.LocationName as "startLocation", l2.LocationName as "endLocation" from Taxi t join Location l1 on t.startLocationId=l1.locationId join Location l2 on t.endLocationId=l2.LocationId