Hey,
I have 3 tables - one to many rel.
tblreports
reportid PK
lotsmorefieldsforfiltering
tblquestioncomments
questioncommentid PK
reportid FK
questionid FK
tblquestions
questionid PK
Originally I just needed a question count based off a specific question ID. As
my loop's output generated question ID's, I ran a query like this to get a
count.
SELECT COUNT(questionid) as numcomments
FROM tblquestioncomments
WHERE questionid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#questionid#">
Now I need to add some filters to it that filter off of tblreports criteria.
I've got this but it doesn't work:
SELECT tblreports.reportid, COUNT(*) as numcomments,
tblquestioncomments.questionid
FROM tblreports, tblquestioncomments
WHERE tblreports.reportid = tblquestioncomments.reportid
AND questionid = <cfqueryparam cfsqltype="cf_sql_integer"
value="#questionid#">
<!--- Filters goe here --->
<cfif FORM.tier7 NEQ "All">
AND tblreports.orgtier7id = '#FORM.tier7#'
</cfif>
<cfif FORM.tier6 NEQ "All">
AND tblreports.orgtier6id = '#FORM.tier6#'
</cfif>
GROUP BY tblquestioncomments.questionid, tblreports.reportid
How can I make this query give me a count of comments using the criteria
provided?
Thanks,
Will
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:294122
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
