The problem is most likely that you are using an implicit cartesian join on
these three tables:
vvl_Counties ,
vvl_districts,
vvl_schools,
since they are not in the WHERE clause.
Remove them and you should get the desired results
------------------------------------------------------------
<CFQUERY DATASOURCE="#dbname#" NAME="qGetSectionTwo">
SELECT
COUNT(ChildIdentity.ChildID) AS TotalPerGender,
ChildIdentity.ChildGender AS Gender
FROM
ChildSchoolHistory CSH,
ChildIdentity CI,
ChildResidence CR
WHERE
CI.ChildID = CR.ChildID AND
CI.ChildID = CSH.ChildID AND
Year(CSH.SchoolYearStartDate) BETWEEN #FORM.SchoolYear# AND
CSH.schooltitle1 = 1 AND<!--- Not sure what value --->
CSH.LastAttendDate IS NULL AND
CR.ResidenceCounty = '#FORM.counties#'
GROUP BY CI.ChildGender
ORDER BY CI.ChildGender
</CFQUERY>
-------------------------------------------------------------
Bryan Love ACP
Internet Application Developer
[EMAIL PROTECTED]
-----Original Message-----
From: John Wilker [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, March 28, 2001 10:34 AM
To: CF-Talk
Subject: Query boggle
Hey guys and gals,
This is probably something I'm just not seeing clearly but the below
query is supposed to bring my back a count of records grouped by sex.
I've been staring at this query so long I'm not seeing anything. It finds
the right records. The data I have has a male and a female record and both
are found by this query. Yet when I output TotalPerGender I get 808233984
Much obliged.
<CFQUERY DATASOURCE="#dbname#" NAME="qGetSectionTwo">
SELECT
COUNT(ChildIdentity.ChildID) AS TotalPerGender,
ChildIdentity.ChildGender AS Gender
FROM
vvl_Counties ,
vvl_districts,
vvl_schools,
ChildSchoolHistory CSH,
ChildIdentity INNER JOIN ChildResidence ON ChildIdentity.ChildID =
ChildResidence.ChildID
WHERE
ChildIdentity.ChildID = CSH.ChildID
AND Year(CSH.SchoolYearStartDate) BETWEEN #FORM.SchoolYear#
AND CSH.schooltitle1 = 1<!--- Not sure what value --->
AND CSH.LastAttendDate IS NULL
AND ChildResidence.ResidenceCounty = '#FORM.counties#'
GROUP BY ChildIdentity.ChildGender
ORDER BY ChildIdentity.ChildGender
</CFQUERY>
J.
John Wilker
Web Applications Consultant
Allaire Certified ColdFusion Developer
Office: 909-943-8428
www.red-omega.com
15 people are known to have been crushed to death tilting soda
machines towards them in order to receive free soda.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
