Best unofficial Apache Server developers community
Username
Forgot password?
Sign in with Twitter account
Sign in with Facebook account

Query to select users who has answered all questions from set of answers of each question

1

36 views

This is mysql table.

            User    Question    Answer
            20      1           answer1_2
            20      2           answer2_5
            20      3           answer3_1
            726     1           answer1_2
            726     2           answer2_5
            726     3           answer3_1

I want the users who has given answer of all questions and we have predefined set of answers for each question.

like Question 1 can have answers from answer1_1, answer1_2, answer1_3 Question 2 can have answers from answer2_1, answer2_2, answer2_3

Initially, I tried query like this:

            SELECT GROUP_CONCAT( DISTINCT CAST( User AS CHAR ) ) allUsers, COUNT( DISTINCT User ) totalUsers
            FROM survey
            WHERE
            (
                (
                Question =1
                AND Answer =  'answer1_2'
                )
                OR (
                Question =1
                AND Answer =  'answer1_2'
                )
                OR (
                Question =1
                AND Answer =  'answer1_3'
                )
            )
            AND (
                (
                Question =2
                AND Answer =  'answer2_1'
                )
                OR (
                Question =2
                AND Answer =  'answer2_2'
                )
                OR (
                Question =2
                AND Answer =  'answer2_3'
                )
            )

but then I see the result and realize that 'AND' among all questions won't work for my requirement but I need the result with that logic.

Table structure cannot be changed.

asked May 24, 2011 2:24 am CDT
posted via StackOverflow

2 Answers

1
 

You could do something like:

Select user, count(answer) from survey group by user having count(answer)=3

answered May 24, 2011 3:23 am CDT
0
 

If you have a unique key on fields User and Question, then you could do something like this:

SELECT count(Question) answers_no, .... FROM survey GROUP BY User HAVING answers_no = num_total_questions

You could pass num_total_questions as a variable from application

answered May 24, 2011 3:23 am CDT

Your answer

Join with account you already have


Sign in with Twitter account
Sign in with Facebook account
Sign in with Google Friend Connect

Preview
Similar questions
A select question
May 23, 2011