please guys help me to get this query

[Roman10]

Hi guys

 

I have 3 tables which are (flight, model, airplane)

Flight: fid, pid, aid, plannedDeparture, actualDeparture, passengerCount, destination

Model: mid, name, usefulLoad, pilotCount 

Airplane: aid, mid, oid, hid, dateAqu

 

and I need to generate a report (query) for overloaded flights. Assuming the following:

 - the passenger/pilot weight is 160 lb on average;
 - fuel takes 65% of the useful load.

Including in the report the following data: flightID (fid), destination, plannedDeparture, passengerCount, airplaneID (aid), airplaneModel (Model.name), usefulLoad, totalLoad (calculated), overLoad (calculated), passengerSurplus (calculated).

 

 

and I'm really sticking on it, I'm not an advanced user on mysql so I would really appreciate helping me here.

 

** I attached my schema ..

p3-GolfClub-DB.txt

[dalecosp]

Where is it you're taking your classes, again?

[Barand]

Have I got this right?

 

Take the Piper Archer TX with a usefulLoad of 870

Fuel is 65% which leaves (870 * 0.35) for passengers and pilot ie 304.5

Take out the pilot and that leaves an allowance of (304.5 - 160) 144.5 for passengers.

 

This is less than the 160 average so cannot carry passengers, or, at least, is overloaded with a single passenger ???

[Roman10]

Yeah I'm totally lost on it

[Barand]

try

SELECT f.fid
, f.destination
, f.plannedDeparture
, f.passengerCount
, f.aid
, m.name
, m.usefulLoad
,  m.usefulLoad*0.65 + (m.pilotcount + f.passengercount)*160 as totalLoad
, CASE 
    WHEN m.usefulLoad*0.65 + (m.pilotcount + f.passengercount)*160 > usefulLoad 
        THEN m.usefulLoad*0.65 + (m.pilotcount + f.passengercount)*160 - usefulLoad
    ELSE 0
    END as overLoad
, CASE 
    WHEN m.usefulLoad*0.65 + (m.pilotcount + f.passengercount)*160 > usefulLoad 
        THEN CEIL((m.usefulLoad*0.65 + (m.pilotcount + f.passengercount)*160 - usefulLoad)/160)
    ELSE 0
    END as passengerSurplus
FROM flight f
INNER JOIN airplane a USING (aid)
INNER JOIN model m USING (mid)
ORDER BY plannedDeparture