Multiple INEER JOINS between two table.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple INEER JOINS between two table.

Author

Topic

jahabdank
Starting Member

2 Posts

Posted - 2007-09-16 : 07:04:28

Hello Everyone,

I have a quite unusual problem, and I have hard time finding the answer.

I have a table with Locations - lets say that it has just ID, and Name,
and a Transport table containing the ID, ArrivalLocationID and DepartureLocationID.

Now - when I select the Transport table I want to get names of the Arrival and Departure locations from th other table.

If it was a single link I woul do an INNER JOIN like:

SELECT
Transport.*,
Locations.Name AS ArrivalLocation
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
Transport.ArrivalLocation = Locations.ID

But I want to do a double INNER JOIN between two same table. And here I get a problem - how to do it? Something like:

SELECT
TransportProductOperationPeriods.*,
Locations.Name AS LArrivalLocation,
Locations.Name AS LDepartureLocation,
Locations.ID AS LArrivalLocationID,
Locations.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations ON
TransportProductOperationPeriods.ArrivalLocation = LArrivalLocationID
INNER JOIN Locations ON
TransportProductOperationPeriods.DepartureLocation = LDepartureLocationID

but obviously that does not work ;)

Thanks in advance,
Joseph

Kristen
Test

22859 Posts

Posted - 2007-09-16 : 07:38:54

You need to provide Alias names for your tables, so that references to them are un-ambigous:


SELECT 
TransportProductOperationPeriods.*,
LA.Name AS LArrivalLocation,
LD.Name AS LDepartureLocation,
LA.ID AS LArrivalLocationID,
LD.ID AS LDepartureLocationID
FROM TransportProductOperationPeriods
INNER JOIN Locations AS LA
ON LA.LArrivalLocationID = TransportProductOperationPeriods.ArrivalLocation 
INNER JOIN Locations AS LD
ON LD.LArrivalLocationID = TransportProductOperationPeriods.DepartureLocation

Kristen

jahabdank
Starting Member

2 Posts

Posted - 2007-09-16 : 11:26:40

Thanks a *lot*. That was exactly what I needed,

Joseph

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources