Need Help with Left Outer Join

[Guest]

I have a list of courses that clients have signed up for. When I click on the view button, it lists all attendees for the course, shows a remove button and once they have completed a survey, it will show the survey button for perusal.

The surveys that are showing up on the right are not the surveys for this particular course. My dilemma is that each student may have taken more than one course, thus completing more than one survey. So, an earlier survey is showing instead of the current survey.

I have two tables that I have joined by a left out join, but I am still trying to wrap my head around this concept.

Signups	Course Eval
signup_id – Primary Key rid – courseID userID courseTitle property calendardate company firstname, lastname, email phone	eval_id, - primary key rid - courseID userID

The signups table is left outer joined to the course_eval table by the query below:

The #rid#, which is the courseID is sent from the previous page.

<cfquery name="getsignups" datasource="#application.dsn#">

select signups.courseTitle,signups.property,signups.calendardate,signups.company,signups.firstname,signups.lastname,signups.email,signups.phone,signups.userID,signups.signup_id,signups.rid,course_eval.userID,course_eval.eval_id

from signups

left outer join course_eval ON signups.userID = course_eval.userID

where signups.rid = #rid#

</cfquery>

My Output:

<cfoutput>

<cfloop query="getsignups">

<TR>

<td>#rid# - #firstname# #lastname#|<a href="mailto:#email#">#email#</a></td>

<td>Ph: #phone#</td>

<td width="24"><a href="showsignups.cfm?signup_id=#signup_id#&go=go" class="button">Remove</a></td>

<cfif eval_iD GT ""><td width="24"><a href="../../forms/surveys.cfm?userID=#userID#" class="button">Survey</a></td><cfelse><td width="24"> </td></cfif>

</tr>

</cfloop>        

</cfoutput>

How do I get the appropriate course to show ?

[Carl Von Stetten]

Rick,

The join should be on the "userID" AND the "rid", otherwise you'll get multiple records from the course_eval table (one for each course a particular student has signed up for).  So this line:

     left outer join course_eval ON signups.userID = course_eval.userID

should be

     left outer join course_eval ON signups.userID = course_eval.userID AND signups.rid = course_eval.rid

A few other suggestions:

• <cfqueryparam> the #rid# in your where clause for performance and protection from SQL injection.
• If you change <cfoutput> to <cfoutput query="getsignups"> you can remove the inner loop entirely.

HTH,

-Carl V.

[rickclark54]

As usual Carl, you are my ace in the hole. Thanks

[BKBK]

@Rickclark54

Please kindly mark the answer as correct. It will anable someone else to find it quicker.

Thanks.