Re: Re: Database Question

No, because the query specifies SELECT *, COUNT ...

      ^

and GROUP BY p.pilot_id Just try it José Pereira wrote:

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