Retrieving one too many records

[elkay]

I am working on a project that will show ... if there is a policy make the policy name a hyperlink else just list the policy name. Here is the code:

<cfquery datasource=&quot;#ds#&quot; name=&quot;getTitle&quot;>
SELECT TitleID, TitleDesc
FROM Title
</cfquery>

<table border=&quot;0&quot;>

<cfloop query=&quot;getTitle&quot;>
<tr>
<td class=&quot;subfour&quot;>

<!--- Display the Policy title --->
<cfoutput><b>#TitleDesc#</b></cfoutput></td></tr>


<cfquery datasource=&quot;#ds#&quot; name=&quot;getTitleBody&quot;>
SELECT *
FROM qryGetTitles1
WHERE Ttitle = #getTitle.TitleID#
</cfquery>

<cfoutput query=&quot;getTitleBody&quot;>

<cfif BodyID eq url.BBody>
<cfset TD = #replace(TitleDesc,&quot; &quot;, &quot;%20&quot;, &quot;all&quot#>

<form action=&quot;printpolicies.cfm&quot; method=&quot;post&quot;>

<!--- if the Policy Title is there, display it as a hyperlink to their respective policies --->

<input type=&quot;checkbox&quot; name=&quot;PrintMe&quot; value=&quot;#PolicyID#&quot;>
<a href = &quot;BodyPolicyText.cfmTitleDesc=#TD#&PolicyID=#PolicyID#&quot;>#TitleDesc#</a></td></tr>

</cfif>
</cfoutput>


The results are:

Academic Dishonesty (this is a hyperlink)
Academic Dishonesty (this one is not a hyperlink)
Advising Requirements
Appeals of Registration or Records Policies
Auditor Status

The &quot;Academic Dishonesty&quot; title that is not a hyperlink should not show up. What might I be doing wrong?

Thanks in advance for all your help,
elkay

 

[csteinhilber]

According to your code, it will, indeed, output the #TitleDesc# twice... once just because that's what you told it to do:

<!--- Display the Policy title --->
                             <cfoutput><b>#TitleDesc#</b></cfoutput></td></tr>

and once if BodyID equals url.BBody:

<cfif BodyID eq url.BBody> 
<cfset TD = #replace(TitleDesc,&quot; &quot;, &quot;%20&quot;, &quot;all&quot;)#>
                    
<form action=&quot;printpolicies.cfm&quot; method=&quot;post&quot;>

<!--- if the Policy Title is there, display it as a hyperlink to their respective policies --->    
                        
<input type=&quot;checkbox&quot; name=&quot;PrintMe&quot; value=&quot;#PolicyID#&quot;>
<a href = &quot;BodyPolicyText.cfmTitleDesc=#TD#&PolicyID=#PolicyID#&quot;>#TitleDesc#</a></td></tr>

I'm guessing, what you want is something more like:

<cfquery datasource=&quot;#ds#&quot; name=&quot;getTitle&quot;>
SELECT TitleID, TitleDesc
FROM Title
</cfquery>    

<form action=&quot;printpolicies.cfm&quot; method=&quot;post&quot;>
<table border=&quot;0&quot;>            
                    
<cfloop query=&quot;getTitle&quot;>
<tr>
<td class=&quot;subfour&quot;>
                
<cfquery datasource=&quot;#ds#&quot; name=&quot;getTitleBody&quot;>
SELECT * 
FROM qryGetTitles1
WHERE Ttitle = #getTitle.TitleID#
</cfquery>            

<cfoutput query=&quot;getTitleBody&quot;>
        
<cfif BodyID eq url.BBody> 
<cfset TD = #replace(TitleDesc,&quot; &quot;, &quot;%20&quot;, &quot;all&quot;)#>

<!--- if the Policy Title is there, display it as a hyperlink to their respective policies --->    
                        
<input type=&quot;checkbox&quot; name=&quot;PrintMe&quot; value=&quot;#PolicyID#&quot;>
<a href = &quot;BodyPolicyText.cfmTitleDesc=#TD#&PolicyID=#PolicyID#&quot;>#TitleDesc#</a></td></tr>

<CFELSE>

<!--- Display the Policy title --->
                             <cfoutput><b>#TitleDesc#</b></cfoutput></td></tr> 
                             

</cfif>
</cfoutput>
</cfloop>
</table>

There are a lot of ways that you could probably simplify your life with regards to this code (not the least of which is doing a JOIN on your select instead of doing successive queries), but the above is the most straight-forward. Rudy can fill you in on JOINs better than I can, if you're interested. Hope it helps,
-Carl

 

[jeepxo]

your join would be

Select Title.TitleID, Title.TitleDesc, qryGetTitles1.Otherstuff from (Title Left outer join qryGetTitles1 on Title.TitleID = qryGetTitles1.TitleID)


I used Otherstuff as a generic thing. You will need to specify each table from qryGetTitles that you want.

I used a left outer join because in your example you are taking all of the TitleIDs from the table Title, if that TitleID is also in qryGetTitles1 then you chose the information there as well.

this join should get all of the TitleIDs from Title, and only the TitleIDS that match in qryGetTitles.