CODEWITHSUNDEEP
mysqlsqlselectjoinleft-join
user2254399
user2254399

Reputation: 5

SQL different condition multi select

What I want to do (for each flight), is to select Flight_number, Departure_airport' s Name And Arrival_airport' s Name . Departure has MIN Leg_number, Arrival has MAX Leg_number.

I have tried this. But join parts or what else missing, here is the link:http://sqlfiddle.com/#!2/263a2/5

Upvotes: 0

Views: 81

Answers (2)

xQbert
xQbert

Reputation: 35323

Seems odd.. but this might be what you're after... We get the min/max leg for each flight in subquery aliased "Z" We use this to join back to flight_leg twice, once for departure and once for arrivals and again join back twice to airport once for departures once for arrivals.

 SELECT Z.Flight_Number, DA.Name DeptName, AA.Name ArrivName
    FROM (SELECT MIN(Leg_Number) MLN, MAX(Leg_Number) MxLN, Flight_Number
               FROM Flight_Leg Group by Flight_Number) Z
    INNER JOIN Flight_Leg D
     on D.Flight_Number = Z.Flight_Number 
    and D.Leg_Number = Z.MLN
    INNER JOIN Flight_Leg A
     on A.Flight_Number = Z.Flight_Number
     and A.Leg_Number = Z.MxLN
    INNER JOIN AirPort DA
     on DA.AirPort_Code = D.Departure_AirPort_Code
    INNER JOIN AirPort AA
     on AA.AirPort_Code = A.Arrival_AirPort_Code

http://sqlfiddle.com/#!2/263a2/56

Upvotes: 1

FrugalShaun
FrugalShaun

Reputation: 176

Not entirely sure if this is what you're after. It's written in MS SQL so the syntax will need some minor tweaks. 

SELECT fl.Flight_number,
ao.Name,
ai.Name,
(select min(Leg_number) from FLIGHT_LEG fa where fl.Flight_number
    = fa.Flight_number) as min_leg_number,

(select max(Leg_number) from FLIGHT_LEG fb where fl.Flight_number
    = fb.Flight_number) as max_leg_number
FROM Flight_leg Fl
inner join AIRPORT as ao on fl.Departure_airport_code =
    ao.Airport_code
inner join AIRPORT as ai on fl.Arrival_airport_code =
    ai.Airport_code

Upvotes: 0

Related Questions