CODEWITHSUNDEEP
mysqlsqldatabase
user1026996
user1026996

Reputation:

MySQL joining table to itself and comparing results

MySQLFiddle here: http://sqlfiddle.com/#!2/15d447/1

I have a single table I am trying to work with:

Table 1: user_answers table (stores users answers to various questions)

The notable values that are stored are the users id (column uid), the question id for the question they are answering (column quid), the answer to the question (column answer) and the importance of their answer (column importance).

The end result I want:

I'd like to be able to grab all the questions that any two users have answered, excluding any answers from questions that have either not been answered by the other party, or answers to the same question which have a value of 1 for either user in importance. Again, this will only ever be used to compare two users at a time.

I've been pretty unsuccesful in my attempts, but here is what I've tried, just piecing things together:

#attempt one: trying to exclude answers that were not answered by both users
SELECT * FROM user_answers AS uid1
JOIN user_answers AS uid2 ON uid1.uid = uid2.uid
WHERE uid1.uid = 1
AND uid2.uid = 20008
AND uid1.quid IS NOT NULL
AND uid2.quid IS NOT NULL;

This returns no results but I'm not exactly sure why.

#attempt two: trying to exclude where answers are the same for both users
SELECT * FROM user_answers AS uid1
LEFT JOIN user_answers AS uid2 ON (uid1.uid = uid2.uid AND uid1.answer <> uid2.answer)

This gives me results, but seems to be doubling up on everything because of the join. I also tried in this attempt to eliminate any answers what were the same, which seems to be working in that sense.

Any guidance is appreciated.

Thanks.

Upvotes: 6

Views: 815

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270201

You can answer your question using an aggregation query. The idea is to using the having clause to filter the rows for the conditions you are looking at.

Because you are not interested at all in questions with importance = 1 those are filtered using a where clause:

select ua.quid
from user_answers ua
where importance <> 1 and uid in (1, 20008)
group by ua.quid
having sum(uid = 1) > 0 and
       sum(uid = 20008) > 0;

If you want to include the answers, you can do:

select ua.quid,
       group_concat(concat(uid, ':', answer) order by uid) as answers

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23381

Just a simple version of what you need.

select *
 from user_answers a,
      user_answers b
where a.quid = b.quid
  and a.uid <> b.uid
  and 1 not in (a.importance, b.importance)

If you like to filter just the questions just change the * for distinct a.quid

See it here on fiddle: http://sqlfiddle.com/#!2/15d447/15

Upvotes: 0

Related Questions