Coldfusion and SQL

Posts: 98 · Posted: Wed Oct 17, 2007 12:19 am

I have this query on MS SQL 2005

Code:

SELECT tbl_category.catname, sum(tbl_answered_questions.points) as points
FROM tbl_answered_questions, tbl_question, tbl_category
WHERE tbl_answered_questions.qid = tbl_question.qid
AND tbl_question.catid = tbl_category.catid
AND tbl_answered_questions.uid = 2
GROUP BY tbl_category.catname

on the MS SQL Management Studio works fine, but when I try to display on the cfoutput I get the wrong output. I get 4

but since I group I should get
Group 1: 0
Group 2: 2
Group 3: 2

Here is my output

Code:

<cfoutput query="qryGetPoints">
<tr>
<td width="14%" height="10" class="myaccount2">#points#</td>
<td width="86%" height="10" class="myaccount3"></td>
</tr>
</cfoutput>

Posts: 1029 · Posted: Wed Oct 17, 2007 10:01 pm

Why don't you try using a proper join?

Code:

SELECT category.catname, sum(answer.points) AS points
FROM tbl_answered_questions AS answer
INNER JOIN tbl_question AS question ON tbl_answered_questions.qid = tbl_question.qid
INNER JOIN tbl_category AS category ON tbl_answered_questions.catid = tbl_category.catid
WHERE tbl_answered_questions.uid = 2
GROUP BY category.catname
ASC

Or something like that?