CODEWITHSUNDEEP
mysqljoin
Barbs
Barbs

Reputation: 1115

MySQL JOIN two rows into one?

How do I make a query that selects the majority of data from 1 row but joins one column from another?

One table "flight_schedules" has:

Flight | Dep_City | Arr_ City | Dep_Time | Arr_Time
---------------------------------------------------
901    | Chicago  | Miami     | 0600     | 0900
902    | Miami    | Chicago   | 0945     | 1300

Another table "airports" has:

City     | Airport_Code
-----------------------
Chicago  | KORD
Miami    | KMIA

My Query atm is something like this:

SELECT ap.Airport_Code as dep_code, ap.Airport_Code as arr_code, fs.* FROM flight_schedules fs JOIN aiports ap ON (fs.Dep_City=ap.City OR fs.Arr_City=ap.City) ORDER BY flight_number

I know this gives me 2 rows per flight like this:

arr_code | dep_code | Flight | Dep_City | Arr_ City | Dep_Time | Arr_Time
-------------------------------------------------------------------------
KORD     | KORD     | 901    | Chicago  | Miami     | 0600     | 0900
KMIA     | KMIA     | 901    | Chicago  | Miami     | 0600     | 0900
KMIA     | KMIA     | 902    | Miami    | Chicago   | 0945     | 1300
KORD     | KORD     | 902    | Miami    | Chicago   | 0945     | 1300

What I am realy after is this:

arr_code | dep_code | Flight | Dep_City | Arr_ City | Dep_Time | Arr_Time
-------------------------------------------------------------------------
KORD     | KMIA     | 901    | Chicago  | Miami     | 0600     | 0900
KMIA     | KORD     | 902    | Miami    | Chicago   | 0945     | 1300

Is there a way to adjust my query to achieve this or am I doomed to including the airport codes in the flight_schedules table (which will require some scripting)? Or...hitting the database for every flight in the flight_schedules table (about 3000)? Which while I could do but a query solution would be more elegant and use less resources.

Thanks in advance for any help.

Upvotes: 0

Views: 72

Answers (2)

Barbs
Barbs

Reputation: 1115

Thank you again Gordon. I tried the EDIT but it didn't really change much so I tried this and got what I wanted...

SELECT DISTINCT fs.id, apdep.Airport_Code as dep_code, aparr.Airport_Code as arr_code, fs.*
FROM flight_schedules fs JOIN
 aiports apdef
 ON fs.Dep_City = apdef.City join
 airports aparr
 on fs.Arr_City = aparr.City
ORDER BY flight_number;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269813

You want to join twice to the table to get the code for arrival city and departure city separately:

SELECT apdep.Airport_Code as dep_code, aparr.Airport_Code as arr_code, fs.*
FROM flight_schedules fs JOIN
     aiports apdef
     ON fs.Dep_City = apdef.City join
     airports aparr
     on fs.Arr_City = aparr.City
ORDER BY flight_number;

EDIT:

If you are looking for one row per flight, then add a where clause:

SELECT apdep.Airport_Code as dep_code, aparr.Airport_Code as arr_code, fs.*
FROM flight_schedules fs JOIN
     aiports apdef
     ON fs.Dep_City = apdef.City join
     airports aparr
     on fs.Arr_City = aparr.City
WHERE dep_city < arr_city
ORDER BY flight_number;

This ensures that the pair is in alphabetical order . . . so only one pair will be in the output.

Upvotes: 1

Related Questions