0

not sure if this is possible. I have two tables. Flight_Booking and Frequent_Flyer.

Flight_Booking has Passenger_Name and Ticket_ID fields. Frequent_Flyer has Passenger_Name as a field.

Is it possible to join (or otherwise connect) the two tables to produce a table that lists all Flight_Booking's and Frequent_Flyers - even when the frequent flyer isn't listed in the Flight_Booking table?

Desired Output

Thanks for any help!

ScoobyShnacks
  • 65
  • 1
  • 9
  • Do some research on using the LEFT JOIN syntax – Andrew Oct 06 '14 at 09:57
  • Tip (not solution): Introduce a frequent flyer identification number in both tables as names do not uniquely identify people and follow the hint of @Andrew. – Barry Oct 06 '14 at 10:02
  • Hi Andrew, I cannot get the desired output using LEFT JOIN. See the example at row 3 where Bart Simpson does not have a corresponding Ticket_ID in the Flight_Booking table. I can't get it to work like that with LEFT JOIN – ScoobyShnacks Oct 06 '14 at 10:05
  • Barry - yes this is implemented I just over simplified the example perhaps! – ScoobyShnacks Oct 06 '14 at 10:06
  • Left join should work, but what is the desired output: Name, Ticket Id, Frequent Flyer (as yes/no)? – Barry Oct 06 '14 at 10:09
  • @Barry The attached image would be the desired output if possible. Basically a list of Flight_Bookings with Frequent Flyers included even if they are not currently booked into the Flight_Bookings table? Thanks PS Frequent Flyer is not a boolean field it is a separate table with the NAMES of frequent flyers or ID's of them. – ScoobyShnacks Oct 06 '14 at 10:13
  • 1
    Ok, so as I understand correctly you want to see all entries from booking, all entries form frequent flyer and the data 'connected' if in both. In that case you should use the full outer join. [Check this](http://www.w3schools.com/sql/sql_join_full.asp) – Barry Oct 06 '14 at 10:21
  • 1
    And [How to emulate them in MySQL](http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql) – Barry Oct 06 '14 at 10:28
  • @Barry thanks, I'll report back :) – ScoobyShnacks Oct 06 '14 at 10:34
  • Select * FROM Flight_Booking LEFT JOIN Frequent_Flyer ON Flight_Booking.Passenger_ID = Frequent_Flyer.Passenger_ID UNION SELECT * FROM Flight_Booking RIGHT JOIN Frequent_Flyer ON Flight_Booking.Passenger_ID = Frequent_Flyer.Passenger_ID; Was the solution thanks :) – ScoobyShnacks Oct 06 '14 at 10:58

1 Answers1

0
Select * FROM Flight_Booking LEFT JOIN Frequent_Flyer ON Flight_Booking.Passenger_ID = Frequent_Flyer.Passenger_ID UNION SELECT * FROM Flight_Booking RIGHT JOIN Frequent_Flyer ON  Flight_Booking.Passenger_ID = Frequent_Flyer.Passenger_ID;

Was the solution thanks @Barry :)

ScoobyShnacks
  • 65
  • 1
  • 9