Mohammad Mohammad - 1 year ago 136
SQL Question

MYSQL: select by two foreign keys from the same table

I have the following tables:

flights: id, idcompany, idplane, fromCity, toCity, takeoff...

companies: id, name

planes: id, name

cities: id, name

I want to declare the names of the two cities "fromCity, toCity" in the same query.

MY purpose of this action is get the cities names at once, so I can display them to the client, without any need to do another query to get the cities names.

here is my try:

SELECT, f.takeoff, f.arrival, as fromCity, f.toCity, as company, as plane
FROM flights f, companies c, planes p, cities ct
WHERE f.idCompany = AND f.idPlane = AND f.fromCity =
ORDER BY f.takeoff ASC

this query will return the name of the "fromCity" but the id of the "toCity", so what I can do to get the two names by the same query?

Answer Source

You could use this:

SELECT, f.takeoff, f.arrival, as fromCity
    , as toCity, as company, as plane 
    flights f
inner join
    companies c
    f.idCompany = 
inner join
    planes p
    f.idPlane =
inner join
    cities ct1 
    f.fromCity = 
inner join
    cities ct2
    f.toCity =   
    f.takeoff ASC;

And try not using old-style-join from now on.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download