normalUser221 normalUser221 -3 years ago 88
MySQL Question

mySQL, WHERE statement for a specific column

i have to select all flights from 'München' to 'Düsseldorf' and back. The table flightexecutions contains the informations to find the correct flight.

With my statement shown below, i am only able to select the FlightNo and the airport "from".

I dont know how to set up my SELECT statement in order to get the desired output which contains the FlightNo, from and to column.

--1)statements works correct, returns all FlightNo and origin "from"
SELECT flx.FlightNo, air.AirportName as `from`, air.AirportName as `to`
FROM flightexecution as flx, airport.airport as air
WHERE flx.ICAO_Code_Origin = air.ICAO_Code and (air.AirportName LIKE 'München (Franz Josef Strauß)'
OR air.AirportName LIKE 'Düsseldorf International');

--2)returns all flightNo and destination "to"
SELECT flx.FlightNo, air.AirportName as `to`
FROM flightexecution as flx, airport.airport as air
WHERE flx.ICAO_Code_Destination = air.ICAO_Code and (air.AirportName LIKE 'München (Franz Josef Strauß)'
OR air.AirportName LIKE 'Düsseldorf International');

output1):

FlightNo | from
-----------------
|

output2):

FlightNo | to
-----------------
|




desired output:

FlightNo | from | to
-------------------------
| |


enter image description here

ERR-Model:

Answer Source

This should do it; it joins all flightexecutions to their origin and destination airports independently, and then uses the WHERE conditions to filter the join results to only those with the desired endpoints.

SELECT fx.FlightNo, o.AirportName AS `from`, d.AirportName AS `to`
FROM flightexecution AS fx
INNER JOIN airport AS o ON fx.ICAO_Code_Origin = o.ICAO_Code
INNER JOIN airport AS d ON fx.ICAO_Code_Destination = d.ICAO_Code
WHERE (o.AirportName = 'München (Franz Josef Strauß)' AND d.AirportName = 'Düsseldorf International')
   OR (d.AirportName = 'München (Franz Josef Strauß)' AND o.AirportName = 'Düsseldorf International')

Sidenote: Due to how (poorly) MySQL handles OR conditions; if the database is large, you may have significantly better performance by UNIONing two versions of this query (one with each OR-part of the WHERE conditions).

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