Its close but it seems to be doing a union rather than a left outer join
(I think!).
On my sample data I have 5 'males' per 'teacher' then the query returns
a count of 25 rather than 5. Gives me an idea though so I will have a
play around with it before I go home tonight.
Thanks,
Grant
Grant Black
Software Developer
SmartMove (NZ) Ltd
Phone: +64 9 361-0219 extn 719
Fax : +64 9 361-0211
Email: [EMAIL PROTECTED]
> -----Original Message-----
> From: Aaron Scott-Boddendijk [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 18, 1999 4:30 PM
> To: Multiple recipients of list delphi
> Subject: Re: [DUG]: Another query
>
>
> >I am having problems with queries again - (I need a decent
> book on SQL -
> >any recommendations?).
>
> I have 'ORACLE: the complete reference' which is a bit out of
> date but has lots
> of DBA type tricks for diagnostics... Covers SQL in a very
> technical manner assuming
> you already understand a great deal... If you've got ORACLE
> it's great otherwise find
> another book...
>
> >I have query that (in a simplified version) does;
> >SELECT Teacher, Students, OtherStuff, Count(MaleFemale) As
> >NumberOfGirls
> >FROM MySchoolTable // a table listing teachers, students
> >WHERE MaleFemale = 'Girl'
> >GROUP BY (Teacher)
>
> something like
>
> SELECT
> PMST.TEACHER Teacher,
> COUNT(MMST.Student) NumberOfBoys,
> COUNT(FMST.Student) NumberOfFemales
> FROM
> MySchoolTable PMST,
> MySchoolTable MMST,
> MySchoolTable FMST
> WHERE
> PMST.Teacher=MMST.Teacher AND MMST.MaleFemale='MALE'
> AND
> PMST.Teacher=FMST.Teacher AND FMST.MaleFemale='FEMALE'
> GROUP BY
> PMST.Teacher;
>
> I think it'll work...
>
> --
> Aaron Scott-Boddendijk
> Jump Productions
> (07) 838-3371 Voice
> (07) 838-3372 Fax
>
>
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
