CODEWITHSUNDEEP
phpmysqlsqldatabase
user1026996
user1026996

Reputation:

MySQL query to select count of conflicts between two users

sqlfiddle here: http://sqlfiddle.com/#!2/bde34

the fiddle data contains:

total conflicting questions between user 1 and user 2: 3
conflicts user 1 has with user 2: 2
conflicts user 2 has with user 1: 2

I have a query that selects the total number of conflicts between to users who have answered the same question. What I am after is to make it show the individual conflict counts user 1 has with user 2 and user 2 has with user 1

For reference:

Table 1: user_answers table (stores users answers and their acceptable asnwers to various questions)

The notable values that are stored are:

users id (column uid)
question id for the question they are answering (column quid)
answer to the question (column answer)
acceptable answer storage #1 stores other acceptable answers for that user (column acceptable_1)
acceptable answer storage #2 stores other acceptable answers for that user (column acceptable_2)
acceptable answer storage #3 stores other acceptable answers for that user (column acceptable_3)
acceptable answer storage #4 stores other acceptable answers for that user (column acceptable_4)
importance of their answer (column importance)

The answer column will store a value between 1 and 4.

The acceptable columns hold selected acceptable answers which are in line with their column placement. For example, user 1 answers 1, but would accept answers 2 and 3. This would make acceptable_2 = 2 and acceptable_3 = 3, leaving acceptable_1 and acceptable_2 with a value of 0. Conflicts happen when two users are compared and one has entered an answer to which the other does not have an acceptable value entered.

Right now I have a query that will grab the count of total number of conflicts between the two users, but I am having trouble getting individual counts. For example how many conflicts does user 1 have specifically when compared to user 2 and vice versa.

For example: 

#select counts of BOTH users total conflicts
SELECT COUNT(*)
    FROM user_answers t1
    JOIN user_answers t2 ON t1.uid > t2.uid AND t1.quid = t2.quid AND
     (FIELD(t1.answer, t2.acceptable_1, t2.acceptable_2, t2.acceptable_3, t2.acceptable_4) = 0 OR
     FIELD(t2.answer, t1.acceptable_1, t1.acceptable_2, t1.acceptable_3, t1.acceptable_4) = 0 )
    WHERE t1.importance <> 1 AND t2.importance <> 1 and t1.uid in (1, 2) AND t2.uid in (1, 2)

This query gives me the total conflicts between user 1 and user 2, but I have not been able to get the individual comparison counts. I have tried removing one of the FIELD functions to try and compare only one user at a time but the results were not what I was expecting.

Any help appreciated.

Upvotes: 0

Views: 324

Answers (2)

Tin Tran
Tin Tran

Reputation: 6202

you can try this one too.

SELECT u1.uid as user,u2.uid as conflict_user,
       sum(FIELD(u1.answer,u2.acceptable_1,u2.acceptable_2,u2.acceptable_3,u2.acceptable_4)=0)
       as conflicts,
       group_concat(IF(FIELD(u1.answer,u2.acceptable_1,u2.acceptable_2,u2.acceptable_3,u2.acceptable_4)=0,u1.quid,NULL))
       as conflicted_questions
FROM user_answers u1
INNER JOIN user_answers u2 ON u1.quid = u2.quid AND u1.uid != u2.uid
GROUP BY user,conflict_user

sqlFiddle

Upvotes: 0

dagfr
dagfr

Reputation: 2384

Try this 

select sum(case(FIELD(t1.answer, t2.acceptable_1, t2.acceptable_2, t2.acceptable_3,     t2.acceptable_4)) when 0 then 1 else 0 end) as conflict_1_over_2,
sum(case(FIELD(t2.answer, t1.acceptable_1, t1.acceptable_2, t1.acceptable_3, t1.acceptable_4)) when 0 then 1 else 0 end) as conflict_2_over_1
     FROM user_answers t1
JOIN user_answers t2 ON t1.uid > t2.uid AND t1.quid = t2.quid
    WHERE t1.importance <> 1 AND t2.importance <> 1 and t1.uid in (1, 2) AND t2.uid in (1, 2)

Upvotes: 1

Related Questions