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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments