CODEWITHSUNDEEP
mysqlsqldatabase
user1026996
user1026996

Reputation:

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

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.

Upvotes: 0

Views: 51

Answers (2)

Bohemian
Bohemian

Reputation: 425043

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.

Upvotes: 0

Jeshurun
Jeshurun

Reputation: 23186

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);

Upvotes: 1

Related Questions