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 *
FROM attendee,
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="125">Profession:</td>
<td colspan="3">
<select name="profession">
<option value="all">All</option>
<cfoutput query="profession">
<option
value="#profession.profession#">#profession.profession#</option>
</cfoutput>
</select>
</td>
</tr>
<tr>
<td width="125">Start Date:</td>
<td width="175"><input type="text" name="start_date"> <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="125">End Date:</td>
<td width="175"><input type="text" name="end_date"> <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="125">Took Training From:</td>
<td colspan="3">
<select name="org_id">
<option value="all">All</option>
<cfoutput query="org_admin">
<option value="#org_admin.id#">#org_admin.org_abbr#</option>
</cfoutput>
</select>
</td>
</tr>
<tr>
<td width="125">Progress:</td>
<td colspan="3">
<select name="progress">
<option value="all">All</option>
<option value="certified">Certified</option>
<option value="failed">Failed</option>
<option value="incomplete">Incomplete</option>
<option value="withdrawn">Withdrawn</option>
</select>
</td>
</tr>
<tr>
<td width="125"> </td>
<td colspan="3" align="left"><input 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=subscribe&forumid=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
