CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Lokesh
Lokesh

Reputation: 21

Multiple choice answer T-SQL query

Query is to get whether the user selected multiple choice answer for a question is right or not, if correct then 1 else 0

I have two tables question_answer and user_exam_answer, entries table has the user submitted answer in column submitted_option_id

user_exam_answer table

question_answer

I tried to write a query when user answer (user_exam_answer) matches with the question answer (question_answer) table

select 
    count(1) as result 
from 
    (select 
         qa.question_id, 
         count(qa.correct_option_id) as col1, 
         count(sa.submited_option_id) as col2 
     from 
         question_answer qa
     left join 
         user_exam_answers sa on (sa.question_id = qa.question_id 
                                  and sa.submited_option_id = qa.correct_option_id 
                                  and sa.exam_id = 'html_001' 
                                  and sa.user_id = 'user_123')
     group by 
         qa.question_id
     having 
         count(qa.correct_option_id) = count(sa.submited_option_id) 
 ) as t

But the problem is when:

but when

I am looking for a query which holds true for all the four condition

Upvotes: 0

Views: 800

Answers (3)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

For each question list the expected answers and the submitted answers (you need a FULL OUTER JOIN to do this, a LEFT join is not enough) and count the number of matches. Then compare this count with the count of the expected answers.

select question_id, case when cnt = sum_test then 1 else 0 end as mark
from (
    select question_id, count(*) cnt, sum(test) sum_test
    from (
        select coalesce(q.question_id, s.question_id) as question_id, 
        correct_option_id, 
        submitted_option_id, 
        case when correct_option_id = submitted_option_id then 1 else 0 end as test
        from question_answer q full outer join user_exam_answer s 
        on q.question_id = s.question_id and q.correct_option_id = s.submitted_option_id
        ) x
    group by question_id
) y

You can find a live demo here

Upvotes: 3

toonice
toonice

Reputation: 2246

Please try the following...

SELECT user_id,
       exam_id,
       question_answer.question_id AS question_id,
       submited_option_id,
       correct_option_id,
       CASE
           WHEN correct_option_id = submited_option_id THEN
               1
           ELSE
               0
       END AS marked_option_id
FROM question_answer
LEFT JOIN user_exam_answer ON question_answer.question_id = user_exam_answer.question_id
ORDER BY user_id,
         exam_id,
         question_id;

This query performs joins the two tables together using a LEFT JOIN so that a record for an an unanswered question is still returned. It then compares the correct answer to the supplied answer for each record and if they match it returns a value of 1, otherwise it will return a value of 0. The results of this comparison are then included in the output as the field marked_option_id. The resulting output is then sorted for convenience of reading.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 1

Steve Lovell
Steve Lovell

Reputation: 2564

it's very unclear what you're trying to do here, but the below should help get you started:

select
    sa.user_id,
    sa.exam_id,
    qa.question_id,
    sa.submitted_option_id,
    qa.correct_option_id,
    case when sa.submitted_option_id = qa.correct_option_id then 1 else 0 end as question_score
from
    question_answer qa     
    LEFT JOIN user_exam_answer sa ON
        uea.question_id = qa.question_id
where
    sa.exam_id='html_001'
    and sa.user_id='user_123'

I'd expect the qa table to also have an exam_id column, but that isn't in your images.

Upvotes: 1

Related Questions