Left join the same table multiple times? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Left join the same table multiple times?

Author

Topic

mr.modus
Starting Member

27 Posts

Posted - 2007-10-05 : 15:41:57

I have a query where I get a Pilot_ID, Crew1_ID, Crew2_ID and Crew3_ID from a table called tbl_Flights. The ID's (int) for these four fields correlate to a table called tbl_AdminUsers. I thought I could just have four LEFT JOIN statements but I get an error telling me:

Tables or functions 'tbl_AdminUsers' and 'tbl_AdminUsers' have the same exposed names. Use correlation names to distinguish them.

I don't know what this means. Here's my query, any help would be great.

SELECT TOP 1 tbl_Flights.*,
tbl_AdminUsers.First_Name + ' ' + tbl_AdminUsers.Last_Name AS Pilot_Name,
tbl_AdminUsers.First_Name + ' ' + tbl_AdminUsers.Last_Name AS Crew1_Name,
tbl_AdminUsers.First_Name + ' ' + tbl_AdminUsers.Last_Name AS Crew2_Name,
tbl_AdminUsers.First_Name + ' ' + tbl_AdminUsers.Last_Name AS Crew3_Name,
tbl_Balloons.Balloon_Name, tbl_Balloons.Balloon_Registration, tbl_Balloons.Balloon_LiftCapacity,
tbl_Balloons.Balloon_PassCapacity, tbl_CityOps.City_Name, tbl_ChaseVehicles.Chase_Vehicle, tbl_ChaseVehicles.Capacity_Pass

FROM tbl_Flights

LEFT
JOIN tbl_AdminUsers
ON tbl_AdminUsers.Admin_ID = tbl_Flights.Pilot_ID

LEFT
JOIN tbl_AdminUsers
ON tbl_AdminUsers.Admin_ID = tbl_Flights.Crew1_ID

LEFT
JOIN tbl_AdminUsers
ON tbl_AdminUsers.Admin_ID = tbl_Flights.Crew2_ID

LEFT
JOIN tbl_AdminUsers
ON tbl_AdminUsers.Admin_ID = tbl_Flights.Crew3_ID

LEFT
JOIN tbl_Balloons
ON tbl_Balloons.Balloon_ID = tbl_Flights.Balloon_ID

LEFT
JOIN tbl_CityOps
ON tbl_CityOps.City_ID = tbl_Flights.City_ID

LEFT
JOIN tbl_ChaseVehicles
ON tbl_ChaseVehicles.Chase_ID = tbl_Flights.Chase_ID

WHERE tbl_Flights.Flight_Date > getDate()
AND tbl_Flights.Pilot_ID = 60
ORDER BY tbl_Flights.Flight_Date

Thanks in advance!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 16:32:39

you have to alias the tables

FROM tbl_Flights
LEFT JOIN tbl_AdminUsers AU1 ON AU1.Admin_ID = tbl_Flights.Pilot_ID
LEFT JOIN tbl_AdminUsers AU2 ON AU2.Admin_ID = tbl_Flights.Crew1_ID
...

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

mr.modus
Starting Member

27 Posts

Posted - 2007-10-05 : 17:41:16

Thanks so much spirit1 for the super fast (and correct) answer.
I'm sure it's a rookie question but I've just learned something new today, which means today is good!
Thanks thanks thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-05 : 17:44:09

that's great and you're welcome, welcome, welcome!

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources