try this: select a.name, b.invitation_status, count(*) from stations a, invitations b where a.id = b.station_id group by a.name, b.invitation_status ----- Original Message ----- From: "Ahmed Farouk" <[EMAIL PROTECTED]> Date: Fri, 29 Nov 2002 22:50:34 +0200 To: <[EMAIL PROTECTED]> Subject: Complex Query
> Hi, > I need help with a complex query that I can't figure it out myself. > > Suppose that I have the following tables > > 1) stations > -id > -name > 2) invitations > -id > -station_id > -invitation_status (confirmed, cancelled, postponed) > > I need a query that generate a report about each station with ther > corresponding number of invitations and a breaddown of inviations to show > total number of each invitation_status > > stations.name | # of invitations | # confirmed | # cancelled | # postponed > ---------------------------------------------------------------------------- > ----------------------------- > station A | 30 | 20 | 5 > | 5 > ---------------------------------------------------------------------------- > ----------------------------- > station B | 25 | 14 | 3 > | 8 > ---------------------------------------------------------------------------- > ----------------------------- > station C | 47 | 29 | 6 > | 12 > ---------------------------------------------------------------------------- > ----------------------------- > > what query should I use to get the previous output, and if I can't get these > results with a direct query how would I manage to generate this report. > > Thanks > Ahmed > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php