Help with a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a query

Author

Topic

Byakuyaromania
Starting Member

2 Posts

Posted - 2010-09-28 : 03:01:19

Can someone help me with some queries?

I have two tables answers and useranswers:

Answers has the fields:

id(auto-increment, primary key)
questionid( id that points to a question)
iscorrect( status of the answer: 1 for correct, 0 for incorrect)

Useranswers has the fields:

id(auto-increment, primary key)
questionid( id that points to a question)
answerid( id that points to the id from the table answers)
sessionid( id for the session)

What i want to do is find out how many wrong answers do i have.
Each question can have multiple correct answers which need to be answered or a single correct answer.

I need a query that can count the wrong answers and there are 3 cases for a wrong answer:
-incomplete answer for a question when we have multiple correct answers;
-when having multiple answers and the user selected too many answers of which one of them is incorrect, even if we have all correct answers;
-when we have single answer for a question and it is incorrect.

Please give me a query that works, i have tried alot of queries and none of them didn't work :(

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 03:17:17

Can you please post some sample data & expected o/p?

PBUH

Byakuyaromania
Starting Member

2 Posts

Posted - 2010-09-28 : 03:43:13

This is the answers table:

id questionid iscorrect
----------------------
1, 1, 1
11, 1, 0
21, 1, 1
31, 31, 1
41, 31, 0
51, 21, 1
61, 21, 0
71, 41, 1
81, 41, 0
91, 41, 1
101, 41, 0
111, 51, 1
121, 51, 0
131, 51, 1

This is for the useranswers table:

id questionid answerid sessionid
---------------------------------------------
9301, 21, 61, 711
9311, 31, 31, 711
9321, 41, 71, 711
9331, 51, 111, 711
9341, 51, 131, 711

The query result should be: 2 because there are 2 wrong answers

the results need to be shown for a session only not for all sessions

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-09-30 : 12:48:44

[code]
SELECT COUNT(*) AS WrongCnt
FROM
(
SELECT u.sessionid,u.questionid,COUNT(*) AS Cnt
FROM answers a
INNER JOIN useranswers u
ON u.questionid = a.questionid
AND u.answerid = a.id
AND a.iscorrect = 1
GROUP BY u.sessionid,u.questionid
)t
INNER JOIN
(SELECT questionid,COUNT(CASE WHEN iscorrect=1 THEN 1 ELSE NULL END) AS Cnt
FROM answers
GROUP BY questionid
)t1
ON t.questionid=t1.questionid
WHERE t.Cnt <> t1.Cnt
AND sessionid=<your sessionid value here>
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources