Eric Bourland
asked on
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:
HTML, generates simple list of Bureaus:
showBureauRecord.cfm:
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
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>
HTML, generates simple list of Bureaus:
<cfoutput query="getBureaus">
<p><a href="/showBureauRecord.cfm?BureauTitle=#getBureaus.BureauTitle#">#getBureaus.BureauTitle#</a></p>
</cfoutput>
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>
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Update: It's working great.
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.getBureauProje cts 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
<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>
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.getBureauProje
_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>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Working great. And this makes sense to me.
_agx_, thank you as always. Have a safe halloween. Take care.
Eric
_agx_, thank you as always. Have a safe halloween. Take care.
Eric
ASKER
New code:
http://www.doivista.org/showBureaus.cfm:
Query:
Open in new window
HTML:
Open in new window
showBureauRecord.cfm:
Query:
Open in new window
HTML:
Open in new window
I see how the JOIN is working. I think I need a GROUP in the <cfoutput query="getBureauProjects">