Mohammad Mohammad - 6 months ago 10
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.id, f.takeoff, f.arrival, ct.name as fromCity, f.toCity, c.name as company, p.name as plane
FROM flights f, companies c, planes p, cities ct
WHERE f.idCompany = c.id AND f.idPlane = p.id AND f.fromCity = ct.id
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

You could use this:

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

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

Comments