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
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.