MySQL joins a table and compares the results

Question

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

I have one table I'm trying to work with:

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

Significant values ​​are user identifiers ( uid column), question identifier for answering a question ( quid column), answer to a question ( answer column) and the importance of their answer ( importance column).

Final result:

I would like to get all the questions that any two users answered, with the exception of answers to questions that the other side did not answer, or answers to the same question, which has a value of 1 for the user in importance . Again, this will only ever be used to compare two users at a time.

In my attempts, I was pretty unsatisfactory, but here is what I tried, just putting 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 does not return results, but I'm not quite 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 the results, but seems to double everything due to the connection. I also tried in this attempt to eliminate any answers, which was the same thing that seems to work in that sense.

Any guidance is appreciated.

Thanks.

Answer 1

You can answer your question using the aggregation request. The idea is to use the having to filter strings for the conditions you are looking for.

Since you are not interested in issues with importance = 1 , they are filtered using the 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 answers, you can do:

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

Answer 2

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 only questions, just change * to distinct a.quid

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