Figuring out a join between two users excluding where column does not exist for one

Question

Sqlfiddle here: http://sqlfiddle.com/#!2/4c532/6

I have a table (user_answers) that holds the answers that users give to questions. In the sqlfiddle we have data for user 1 and for user 2. User 1 has answered 5 questions total and user 2 has answered 8 questions total.

The result I am looking for:
I want to be able to pull how many questions a user has answered that the other one has also answered. An example output would be like this: "User 1 has answered 5 out of the 8 questions you have answered".

I have tried joining the tables but it is giving me more results than I was expecting and I hadn't yet specified which users to test in this query:

SELECT * FROM user_answers t1
JOIN user_answers t2 ON t1.uid > t2.uid
AND t1.answer IS NOT NULL

And then when I tried to specify users I got no results with this query:

SELECT * FROM user_answers t1
JOIN user_answers t2 ON t1.uid > t2.uid
AND t1.answer IS NOT NULL
AND t1.uid = 1
AND t2.uid = 2

I feel like I'm getting closer but could use some guidance.

Thanks.

score 1 · Accepted Answer · answered Jan 23 '14 at 07:01

1

How about using a subquery?

 SELECT * FROM user_answers t1
 where t1.uid=1 and t1.quid in 
 (select t2.quid from user_answers t2 where t2.uid=2);

answered Jan 23 '14 at 07:01

Jeshurun

22,940
6
79
92

This works perfectly. I was playing with a subquery, but I was also reading where it might not perform as well as a join. Is a join still possible in this case or is subquery the best method for the results I am looking for? Cheers. – Jan 23 '14 at 07:47
1

You certainly can replace it with a join doing something like this ` SELECT t1.quid FROM user_answers t1 join user_answers t2 on t1.id= t2.id group by t1.quid having count(*) > 1;`. But I would recommend taking a look at this answer first: http://stackoverflow.com/a/2577188/473637 – Jeshurun Jan 23 '14 at 08:12
This doesn't remotely answer the question. Where are the question totals for the users? Where is the shared question total? – Bohemian Jan 23 '14 at 10:30

Bohemian · Answer 2 · 2014-03-27T12:11:14.440

To find the total number of questions asked by each user, and how many coincided:

SELECT
  count(distinct t1.id) user1_total,
  sum(t1.quid = t2.quid) shared_total,
  count(distinct t2.id) user2_total
FROM user_answers t1
CROSS JOIN user_answers t2
WHERE t1.uid=1
AND t2.uid=2

Using your data, this returns:

USER1_TOTAL SHARED_TOTAL USER2_TOTAL
5           5            7

See SQLFiddle.

Also, any time you can replace a WHERE ID IN (subquery) with a JOIN you should - the join (virtually) always performs better.

Figuring out a join between two users excluding where column does not exist for one

2 Answers2