Need help with complex ranking query

[coloradoguywill]

Ok, I have one last query to get right in my eval app.

Here's my schema:

http://wtomlinson.com/evalschema.gif

The data looks like this:

tblquestions:
Rank these tools in order of learning curve

tblanswersets
CF
.NET
PHP

tblanswers
1
2
3

Answers are inserted into an output table, tblevalanswerresults, where they can be counted.


Here's what I need in plain english:

Show me a list of questions. With each question, show me a list of the answers according to what they were ranked, listed highes ranked to lowest.

Here's a query I'm workin with, the data isn't right tho.

SELECT
Q.questionID,
Q.questionsetID,
Q.question,
Q.questioncatid,
QAX.questionid,
QAX.answersetid,
ASets.answersetid,
ASets.answersettypeid,
ASets.answersetname,
AST.answersettypename,
ASAX.answersetid,
ASAX.answerid,
A.answerID,
A.answername,
A.answervalue,
EAR.answerid,
E.instructorid,
EAR.answerid,
count(*) AS ranking
FROM tblquestions Q
INNER JOIN tblquestionsanswers_x QAX ON Q.questionID = QAX.questionID
INNER JOIN tblanswersets ASets ON QAX.answersetid = ASets.answersetid
INNER JOIN tblanswersettypes AST ON AST.answersettypeid =
ASets.answersettypeid
INNER JOIN tblanswersetsanswers_x ASAX ON QAX.answersetid = ASAX.answersetid
INNER JOIN tblanswers A ON ASAX.answerid = A.answerid
INNER JOIN tblevalanswerresults EAR ON EAR.answerid = A.answerid
INNER JOIN tblevaluations E ON E.evalid = EAR.evalid
WHERE Q.questioncatid = 3 /* Additional Q = 3*/
AND ASets.answersettypeid = 3 /* Rankings only = 3*/
AND E.instructorid = '0103420'
GROUP BY A.answerid, ASets.answersetid ORDER BY ranking


Thanks much, Will

 

[r937]

your GROUP BY is wrong -- guaranteed to fail in every database except mysql, which quite happily executes invalid GROUP BY queries (although they do warn you that results might be unpredictable)

i have a feeling that you don't actually want GROUP BY

why didn't you explain the other tables in the query?

exactly what is COUNT(*) actually supposed to count?

r937.com | rudy.ca