Jump to content


Photo

please guys help me to get this query

mysql

  • Please log in to reply
4 replies to this topic

#1 Roman10

Roman10

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 02 December 2013 - 08:12 PM

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 ..

Attached Files



#2 dalecosp

dalecosp

    Advanced Member

  • Members
  • PipPipPip
  • 306 posts
  • LocationJoplin, MO

Posted 02 December 2013 - 09:31 PM

Where is it you're taking your classes, again? :D
"God doesn't play dice" --- Albert Einstein
"Perl is hardly a paragon of beautiful syntax." --- Weedpacket

#3 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,217 posts
  • LocationCheshire, UK

Posted 03 December 2013 - 07:12 AM

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 ???


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 


#4 Roman10

Roman10

    Newbie

  • New Members
  • Pip
  • 2 posts

Posted 03 December 2013 - 07:31 PM

Yeah I'm totally lost on it



#5 Barand

Barand

    Sen . ( ile || sei )

  • Gurus
  • 14,217 posts
  • LocationCheshire, UK

Posted 04 December 2013 - 03:16 AM

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

Attached Files


|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts
|baaSelect| generate js and php code for dynamic linked dropdowns

 





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com