Re: Re: Database Question

Ok, but won't this count ALL the reports and ALL the hours together??? "Marek Kilimajer" <kilimajer@webglobe.sk> escreveu na mensagem news:3E8D7559.5020800@webglobe.sk... José Pereira wrote: >Sorry I wasn't clear before. It goes like this. > >I have a Virtual Airline and I have a DB called pilots with these tables: > To make it cleaner: database has tables, tables have columns, pilots and report are tables, these are columns: >pilot_id, pilot_name, ivao, vatsim, status > >Also have a report DB which contains these tables: > >pilot_id, pilot_name, aircraft, flight_time, origin, destination, >originweather, destinationweather, etc., etc., (<--- this is per report) > Add report_id here (INT PRIMARY autoincrement) > >I want to get all the information from these tables, for each pilot, since a >pilot has many reports there are many lines in the Report DB. > >Now I need to create a page showing the all the current pilots that active >(stated in the status table of the PILOT DB), and next to that show their >TOTAL reports filed and TOTAL hours flown for the company. > SELECT *, COUNT(report_id) count, # nuber of reports SUM(flight_time) as total_time # total time FROM pilots p LEFT JOIN report r ON p.pilot_d=r.pilot_id # left join so even pilots with no report show up WHERE p.status=1 # only active pilots GROUP BY p.pilot_id # this magic alows us to get info from pilot table together with grouping info (COUNT, SUM...) from reports table ORDER BY pilot_name # order it > >The PILOT_ID must link to another page which will show all the reports in >detail for that pilot. > so be it > >I down't know if this is possible or adviseable, but I have about 20 pilots >sending 2-3 report per day, you can imagine how confusing the REPORT section >is now, since it has all the report for all the pilots ordered by >pilot_id....I have about 160 reports so far, and I'm going nuts.... > >Thanks in advance. >Joe > > > > >