Need Help with Left Outer Join

i have list of courses clients have signed for. when click on view button, lists attendees course, shows remove button , once have completed survey, show survey button perusal.

the surveys showing on right not surveys particular course. dilemma each student may have taken more 1 course, completing more 1 survey. so, earlier survey showing instead of current survey.

i have 2 tables have joined left out join, still trying wrap head around 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 left outer joined course_eval table query below:

the #rid#, courseid sent previous page.

 

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

select signups.coursetitle,signups.property,signups.calendardate,signups.company,signups.firstna me,signups.lastname,signups.email,signups.phone,signups.userid,signups.signup_id,signups.r id,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 appropriate course show ?

rick,

 

the join should on "userid" , "rid", otherwise you'll multiple records course_eval table (one each course particular student has signed for).  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 , signups.rid = course_eval.rid

 

a few other suggestions:

• <cfqueryparam> #rid# in clause performance , protection sql injection.
• if change <cfoutput> <cfoutput query="getsignups"> can remove inner loop entirely.

 

hth,

-carl v.

More discussions in ColdFusion

adobe