thanks, i'll check this out.
JOhn
-----Original Message-----
From: Pascal Peters [mailto:[EMAIL PROTECTED]
Sent: Wednesday, June 16, 2004 2:12 PM
To: CF-Talk
Subject: RE: SQL Help
You are filtering on the enrollment table. This makes the left outer
join useless.
Try doing :
WHERE e.EnrollmentDate BETWEEN '#fromDate#' AND '#toDate#'
OR e.EnrollmentDate IS NULL
And of course .... (Guess what or look at some other threads right now)
> -----Original Message-----
> From: Venable, John [mailto:[EMAIL PROTECTED]
> Sent: woensdag 16 juni 2004 18:25
> To: CF-Talk
> Subject: SQL Help
>
> Hey Folks,
>
> I've got a query that I'm trying to pull some aggregate
> data from and refer to it with array syntax. i.e. #queryname.field[i]#
>
> One query I have isn't pulling all the values that I might
> encounter, thereby throwing the index off for any references
> thereafter. here's my query:
>
> SELECT d.Disability, count(ed.DisabilityID) as Count FROM
> Disabilities d LEFT OUTER JOIN EnrollmentsDisabilities ed
> ON d.DisabilityID = ed.DisabilityID JOIN Enrollments e
> ON ed.ProfileID = e.ProfileID AND ed.EnrollmentNumber
> = e.EnrollmentNumber WHERE e.EnrollmentDate BETWEEN
> '#fromDate#' AND '#toDate#'
> GROUP BY Disability
> ORDER BY Disability
>
>
> EnrollmentsDisabilities is a join table for a many-to-many
> relationship with Enrollments and Disabilities
>
> I want all the possible values of Disability even if the
> count is zero. I thought the 'LEFT OUTER JOIN' syntax would
> take care of that, but it's not working. What am I doing wrong?
>
> Thanks much!
>
> John
>
>
> ---
> John Venable
> Director of Web Architecture
> Epilepsy Foundation
>
>
>
>
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
