harish_sng harish_sng -4 years ago 112
MySQL Question

How can I fetch all possible flights between two cities?

I have a MySQL table called flight details as shown below:

enter image description here

Expected results:


  • Bangalore to Chennai

  • Bangalore to Delhi

  • Bangalore to Kolkata

  • Bangalore to Mumbai

  • Chennai to Bangalore

  • Chennai to Delhi

  • Chennai to Kolkata

  • Chennai to Mumbai

  • Delhi to Chennai

  • Delhi to Bangalore

  • Delhi to Kolkata

  • Delhi to Mumbai

  • Kolkata to Chennai

  • Kolkata to Delhi

  • Kolkata to Bangalore

  • Kolkata to Mumbai

  • Mumbai to Chennai

  • Mumbai to Delhi

  • Mumbai to Kolkata

  • Mumbai to Bangalore


Answer Source

try this,

SELECT fl1.flight_city,fl2.flight_city FROM `flight_details` as fl1 JOIN `flight_details` as fl2 on fl2.fid != fl1.fid 

edit, to add reverse ordering also use,

SELECT fl1.flight_city,fl2.flight_city FROM `flight_details` as fl1 JOIN `flight_details` as fl2 on fl2.fid != fl1.fid or fl1.fid != fl2.fid 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download