1

enter image description here

question id (1,3,2,3) responses id(1,6,4,7)

     Question_Id   Response_Id
               1 =>1
               3=>6
               2=>4
               3=>7

I need to select those distinct users from users_response(following) table who answered all questions mentioned above and have the same answer as mentioned under response_id (1,6,4,7) to the question_id(1,3,2,3) respectively.

Result should be only '2'.

Can you please help me or give me some hint to deal with it. Any dynamic way because count of question_id and response_id can be increased. like in this example here are only total 4 conditions are given but it could go up or down.

 SELECT * FROM users_responses
 WHERE 
   (Question_Id = 1 AND Question_Id = 3) 
 AND Question_Id = 2 AND Question_Id = 3 
 AND (Question_Id = 1 AND Response_Id = 1);

but I'm getting no row while there is at least one row with user_id 2 is fulfilling conditions.

1

There are many solutions for this kind of problem (it's called relational division).

The most simple ways to write it would be either by self-joining the table (4 times) or with a GROUP BY and HAVING clause:

SELECT user_id
FROM users_responses
WHERE (question_id = 1 AND response_id = 1)
   OR (question_id = 3 AND response_id = 6)
   OR (question_id = 2 AND response_id = 4)
   OR (question_id = 3 AND response_id = 7)
GROUP BY user_id
HAVING COUNT(DISTINCT question_id, response_id) = 4 ;

If there is a UNIQUE constraint on (user_id, question_id, response_id), you can simplify the HAVING clause to:

HAVING COUNT(*) = 4 ;
  • This query giving 2 resords user id 2,6 while user 6 has not provided 1,6,4,7 answer to question 1,3,2,3. Result should be only 2 but user id 6 is also coming in output so how to deal with it. – coder Nov 12 '16 at 15:53
  • @coder did you try this exact query? – ypercubeᵀᴹ Nov 12 '16 at 17:12
  • working perfectly , my mistake. can you please tell little bit about the functionality of "having count(DISTINCT question_id, response_id) =4" in this query. @ypercube – coder Nov 13 '16 at 3:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.