Finding "missing" rows

Question

I have an exam database, with 4 tables: users, answers, questions and subjects.

user table
U_id | name

subjects table
S_id | Subject

questions table
Q_id | S_id | question | Correct 

answers table
U_id | Q_id  | answers

I need to find the number of correct answer per subject, per user.

My query

select  U_id,questions.S_id ,count(Q_id) 
from answers inner join questions  on  questions.q_id = answers.q_id
where questions.Correct  = answers.answer
group by answers.U_id,questions.S_id

result:
1 | s1 | 2
1 | s2 | 3
1 | s3 | 1
2 | s1 | 1
2 | s2 | 1

That gives me the number of correct answer per subject, but if the user doesn't have a correct answer the S_id is not display, i need to display those with 0.

The result I need is

result:
1 | s1 | 2
1 | s2 | 3
1 | s3 | 1
2 | s1 | 1
2 | s2 | 1
2 | s3 | 0

Note: I´m using MySQL, but a MS SQL server answer is also fine.

something about the inner join... outer join, cross join... too long ago... Try it. — yankee
– yankee, Commented Feb 6, 2011 at 4:32

RichardTheKiwi · Accepted Answer · 2011-02-06 06:29:58Z

2

select u.U_id, q.S_id, count(a.q_id) 
from users u
cross join questions q
left join answers a on q.q_id = a.q_id and u.u_id = a.u_id and q.Correct = a.answer
group by u.U_id, q.S_id

answered Feb 6, 2011 at 6:29

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Finding "missing" rows

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related