odd results in database query

ColdFusion 9
MS SQL Server 2012

On this page http://www.doivista.org/showBureaus.cfm I'm getting some strange results -- which tells me I have not built my query correctly.

I want to click on the title of a bureau, and then see all of the projects that are associated with the bureau. Instead, I see either zero or one projects. So I did something wrong. I've been staring at this for a while.

Here are the queries I am using:

http://www.doivista.org/showBureaus.cfm:

<cfquery name="getBureaus" datasource="#application.datasource#">
SELECT BureauTitle
FROM #REQUEST.BureauTable#
ORDER BY BureauTitle
</cfquery>

Open in new window


HTML, generates simple list of Bureaus:

 <cfoutput query="getBureaus">
 
<p><a href="/showBureauRecord.cfm?BureauTitle=#getBureaus.BureauTitle#">#getBureaus.BureauTitle#</a></p>

 </cfoutput>

Open in new window



showBureauRecord.cfm:

<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.Bureau
FROM #REQUEST.BureauTable# b, #REQUEST.ProjectsTable# p
WHERE b.bureauTitle = <cfqueryparam value="#url.bureauTitle#" cfsqltype="cf_sql_varchar">
AND b.bureauTitle = p.Bureau
ORDER BY b.bureauTitle
</cfquery>

Open in new window


I think I need to change the query immediately above, in showBureauRecord.cfm. I am working with two tables. Do I need to use a JOIN?

Thanks as always.

Eric
LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
Hi Eric.


>> Do I need to use a JOIN?

If you need to include data from both tables in In showBureauRecord.cfm, yes. You need to include a JOIN here:

SELECT ...
FROM #REQUEST.BureauTable# b 
                 INNER JOIN   #REQUEST.ProjectsTable# p ON  p.ColumnItHasinCommon = b.ColumnItHasinCommon
WHERE ....

Open in new window


>> Instead, I see either zero or one projects


Can you post the cfoutput code for showBureauRecord.cfm?  I suspect it's using cfoutput group="..." and that's suppressing the display of the individual projects. If so, remove the "group" and problem solved.

<cfoutput query="getBureauProjects" group="BureauTitle">
...

Just a suggestion, instead of passing Bureau name - it's better to pass the ID instead. Then you don't have to worry about URL encoding the title string:
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
Got it!

New code:

http://www.doivista.org/showBureaus.cfm:

Query:
<cfquery name="getBureaus" datasource="#application.datasource#">
SELECT bureauID, bureauTitle
FROM #REQUEST.BureauTable#
ORDER BY bureauTitle
</cfquery>

Open in new window


HTML:
<cfoutput query="getBureaus">
<p><a href="/showBureauRecord.cfm?bureauID=#getBureaus.bureauID#">#getBureaus.bureauTitle#</a></p>
</cfoutput>

Open in new window



showBureauRecord.cfm:

Query:
<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.bureauTitle
FROM #REQUEST.BureauTable# b
INNER JOIN  #REQUEST.ProjectsTable# p
ON p.bureauTitle = b.bureauTitle
ORDER BY b.bureauTitle
</cfquery>

Open in new window



HTML:

<cfoutput query="getBureauProjects">
 
<h2>Bureau: #bureauTitle#</h2>
<h3>Associated Projects:</h3>
<p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>

</cfoutput>

Open in new window


I see how the JOIN is working. I think I need a GROUP in the <cfoutput query="getBureauProjects"> ... I am trying out a few things. Making progress. Thank you as always, _agx_. E
0
Eric BourlandAuthor Commented:
Update: It's working great.

<cfquery name="getBureauProjects" datasource="#application.datasource#">
SELECT
b.bureauID,
b.bureauTitle,
p.projectID,
p.ProjectName,
p.bureauTitle
FROM #REQUEST.BureauTable# b
INNER JOIN #REQUEST.ProjectsTable# p
ON p.bureauTitle = b.bureauTitle
WHERE b.bureauID = <cfqueryparam cfsqltype="cf_sql_integer" value="#url.bureauID#">
</cfquery>

Open in new window


I have one more question -- well, two. =)

1. code above is safe vs. injection, correct?

2. one bureau has no projects associated with it yet. I'd like to add a message -- "Currently no projects are associated with this bureau.".

When I do this (below) ... I don't see my "Currently no projects are associated with this bureau." message ... obviously because recordcount.getBureauProjects is, in fact, GT 0. What's a good way to do this?

_agx_ thanks as always. Hope you have fun and merry halloween plans. E

<cfoutput query="getBureauProjects">

<cfif recordcount.getBureauProjects GT 0>
<p><a href="/showProjectRecord.cfm?ProjectID=#val(getBureauProjects.ProjectID)#">#getBureauProjects.ProjectName#</a></p>

<cfelse>
<h2>Currently no projects are associated with this bureau.</h2>

</cfif>

</cfoutput>

Open in new window

0
_agx_Commented:
RE #1

Yes. As long as #REQUEST.BureauTable# and #REQUEST.ProjectsTable# are values you hard code somewhere in your application file.  They would not be safe if they were supplied by user input like a FORM or URL variable.

RE #2

I think it should work if you rearrange the CFIF

<cfif recordcount.getBureauProjects GT 0>
    <cfoutput query="getBureauProjects">
        <p>... show link here </p>
    </cfoutput>
<cfelse>
    <h2>Currently no projects are associated with this bureau.</h2>
</cfif>

Open in new window


You too. Happy Halloween :)
0
Eric BourlandAuthor Commented:
Working great. And this makes sense to me.

_agx_, thank you as always. Have a safe halloween. Take care.

Eric
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.