Wow... join conditions in the where clause... Haven't seen those for years
:)
Looks like you have a cartesian join between organizations and the rest of
your dataset... Need to join organizations to something.
-Original Message-
From: Jillian Carroll [mailto:[EMAIL PROTECTED]
Sent: Thursday, July 03, 2003 6:17 AM
To: CF-Talk
Subject: Conditional SQL Query Not Working
I have a form, that asks for a number of 'conditions' in order to find an
attendee for a course. The problem is, I've put together a query that
returns too many results / not the correct results. Every row returned is
returned multiple times. I'm wondering if I'm needing a join somewhere...
or if there is a more efficient way to make this work. Thank you in advance
for your help!
cfquery name=criteria datasource=#DSN#
SELECT *
FROMattendee,
users,
courses,
organizations
WHERE attendee.users_id = users.id
AND attendee.assigneddate = courses.id
!--- Limit query results by profession ---
cfif form.profession NEQ 'all'
AND users.profession = '#form.profession#'
/cfif
!--- Limit query results by progress in program ---
cfif form.progress EQ 'certified'
AND attendee.gradnum 'Pending'
cfelseif form.progress EQ 'failed'
AND attendee.postassign = 'Failed'
cfelseif form.progress EQ 'incomplete'
AND attendee.gradnum = 'Pending'
cfelseif form.progress EQ 'withdrawn'
AND attendee.withdrawn = '1'
/cfif
!--- Limit query results by organization training was
received from ---
cfif form.org_id NEQ 'all'
AND courses.org = #form.org_id#
/cfif
!--- Limit query results by date ---
cfif (len(form.start_date) GT 1) AND (len(form.end_date) GT
1)
AND courses.s1_startdate #form.start_date#
AND courses.s1_enddate #form.end_date#
/cfif
ORDER BY
users.lname ASC
/cfquery
*** *** ***
The query:
*** *** ***
The form:
form name=find_attendee action=report_attendee.cfm method=post
table tr
td width=125Profession:/td
td colspan=3
select name=profession
option value=allAll/option
cfoutput query=profession
option
value=#profession.profession##profession.profession#/option
/cfoutput
/select
/td
/tr
tr
td width=125Start Date:/td
td width=175input type=text name=start_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'start_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td
td width=125End Date:/td
td width=175input type=text name=end_datenbsp;a
href=javascript:ShowCalendar('find_attendee', 'end_date')img
src=images/cal.gif width=16 height=16 border=0 alt=Click Here to
Pick up the date/a/td /tr tr
td width=125Took Training From:/td
td colspan=3
select name=org_id
option value=allAll/option
cfoutput query=org_admin
option value=#org_admin.id##org_admin.org_abbr#/option
/cfoutput
/select
/td
/tr
tr
td width=125Progress:/td
td colspan=3
select name=progress
option value=allAll/option
option value=certifiedCertified/option
option value=failedFailed/option
option value=incompleteIncomplete/option
option value=withdrawnWithdrawn/option
/select
/td
/tr
tr
td width=125nbsp/td
td colspan=3 align=leftinput type=submit name=submit
value=Search!/td /tr /form
~|
Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
