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.