MySQL correctly compares and excludes results

Question

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

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 answered by any two users, with the exception of answers to questions that were not satisfied by the other side, answers identical between them, or answers to questions the same question, which has a value of 1 for any important user. Again, this will only ever be used to compare two users at a time.

So far I have managed to get results and answers for two users with all my conditions, except for the exception of the answers to which they answered the same way. In the sqlfiddle example, you will see that both users answered quid 2 with the answer "aa". The desired result excludes quid 2 and returns only quid 1.

I am currently using the following query:

 select ua.quid, GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1, GROUP_CONCAT(IF(uid=20008,answer,'') SEPARATOR '') as a2 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; 

With this query, I tried adding WHERE a1 <> a2 to the same line as group_concat, as well as AND a1 <> a2 after the initial WHERE , but could not get this to work.

Any suggestions?

Answer 1

Now I think I understand.

If users have only one answer for each question, then this should work:

 select ua.quid, GROUP_CONCAT(IF(uid=1,answer,'') SEPARATOR '') as a1, GROUP_CONCAT(IF(uid=20008,answer,'') SEPARATOR '') as a2 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 and max(case when uid = 1 then answer end) <> max(case when uid = 20008 then answer end); 

EDIT:

Of course, users can have multiple answers. This pushes me toward a join approach, not a totality. Unfortunately, this approach requires a full outer join , which MySQL does not support. So, first try aggregating the answer. You have only two users, so we will rotate the values:

 select ua.quid, GROUP_CONCAT(IF(user1 > 0, answer, '') SEPARATOR '') as a1, GROUP_CONCAT(IF(user20008 > 0, answer, '') SEPARATOR '') as a2 from (select ua.quid, ua.answer, max(case when ua.uid = 1 then 1 else 0 end) as user1, max(case when ua.uid = 20008 then 1 else 0 end) as user20008 from user_answers ua where importance <> 1 and ua.uid in (1, 20008) group by ua.quid, ua.answer ) t where t.user1 = 0 or t.user20008 = 0 group by ua.quid;