[SOLVED] Double join on the same table, PHP/MySQL

[SnowControl]

Hi all.

 

I've been doing some hobby programming with PHP, and then mainly in use with MySQL. Recently I've been creating a route system for a virtual airline that some of my friends run.

 

Now here's my problem. I have to tables for this, one containing the airports (also called "hubs") and one containing the routes. The routes that are saved in the routes table link to the hubs table for the names and icao codes of each airport. As displayed below:

 

 

I would like to display my data like this (a loop displaying loads of routes):

 

Kennedy (KJFK) -> Dallas (KDFW) | 962.35 nautical miles

Kennedy (KJFK) -> Houston (KIAH) | 113.20 nautical miles

 

So the logical sql query would be as follows:

 

SELECT * FROM routes LEFT JOIN hubs ON routes.departure=hubs.id LEFT JOIN hubs ON routes.arrival=hubs.id

 

This leaves me with a problem though. It now joines the hubs table twice. I now have two variables in the result array that are called "hubs.name". One for the departure airport, and one for the arrival airport. (This also goes for the icao code and the url, anything stored in the hubs table).

 

The database gives me this error:

 

MySQL said:  

#1066 - Not unique table/alias: 'hubs'

 

I can partially fix this by using two queries, but it does not give me the same freedom of organizing the results as I can with only 1 query.

 

 

So can this be done with one query? Somehow renaming the variable so i can extract both? Help is very much appreciated.

[teng84]

the error is telling you that you cant call same index in diff table in your case  u use * wich call all the field not both table has a field id so you have to specify wich id your calling same as the way you do the conditioning for the join

[SnowControl]

the error is telling you that you cant call same index in diff table in your case  u use * wich call all the field not both table has a field id so you have to specify wich id your calling same as the way you do the conditioning for the join

[/quote

 

Could you write me the query? I can't quite see how this would help.

[SnowControl]

Last post got messed up sorry.

 

Could anyone write me the query that teng84 is thinking about in his post?

[teng84]

sample

 

select table1.id,table2.id from table1, table2 where table1.id = table2.id

 

as you can see i include the table name of each field to avoid ambiguous error to avoid mysql for being confuse which field your calling

[SnowControl]

I dont think you really understood my problem.

 

But i fixed it myself. Realized that you could rename tables 'on the fly' in a query. So now everything works great. Thanks for your time people.

 

Final query (bold parts are what did it for me):

 

SELECT * FROM `routes`

LEFT JOIN `hubs` AS `departuretable` ON routes.departure = departuretable.id

LEFT JOIN `hubs` AS `arrivaltable` ON routes.arrival = arrivaltable.id