mysql select when matching multiple rows

Question

I have a project where I need to select only the users that answered to some questions in a certain way (based on a filter).

The filter table (filter) looks like this

   question |   answer  

   Q1       |    A  
   Q2       |    B

The user table (answers) looks like this

user | question | answer  

  1  |    Q1    |    A  
  1  |    Q2    |    D  
  2  |    Q1    |    A  
  2  |    Q2    |    B

How can I select from the user table only the user(s) that match the filter?
I tried

"SELECT user FROM answers WHERE (question = Q1 AND answer = A) 
             AND (question = Q2 AND answer = B)"

and it doesn't work -- I get an empty result. Thank you.

Anup Yadav · Accepted Answer · 2012-12-05 06:56:23Z

3

In your query you are asking to get data which is not present. You are trying to get that user which has both the combination.

Try this

SELECT user FROM answers WHERE (question = 'Q1' AND answer = 'A') 
         OR (question = 'Q2' AND answer = 'B')

edited Dec 5, 2012 at 6:56

answered Dec 5, 2012 at 5:31

Anup Yadav

3,0153 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1735943 Over a year ago

Yes, it is present. I need the user that answered A to Q1 and B to Q2, to match the filter. What you are proposing will look only for one question/answer pair and not for both.

Crystal · Accepted Answer · 2012-12-05 05:00:31Z

2

try this

select answers.user
from filter, answers
where filter.question=answers.question and filter.answer=answers.answer
group by answers.user having count(answers.question)=2

or

select user
from answers
where user not in 
(select distinct a.user from answers a, filter f 
where a.question=f.question and a.answer!=f.answer)

answered Dec 5, 2012 at 5:00

Crystal

722 bronze badges

1 Comment

user1735943 Over a year ago

Both your solutions work. Great job and thanks for taking the time. I have now to see which solution is the fastest.

Sashi Kant · Accepted Answer · 2012-12-05 04:41:43Z

2

Try this:

Select 
DISTINCT(userId)
from user_table ut
inner join filtertable ft on (ut.question=ft.question and ut.answer=ft.answer)

answered Dec 5, 2012 at 4:41

Sashi Kant

13.5k9 gold badges50 silver badges71 bronze badges

3 Comments

Daniel Miladinov Over a year ago

I was going to write almost the same query with the same join conditions, but the OP only wanted to select answer.user.

Dale M Over a year ago

To do that he needs select distinct id

user1735943 Over a year ago

It doesn't work. I get both users and I should get only user 2, because only user 2 completely matches the filter.

Collectives™ on Stack Overflow

mysql select when matching multiple rows

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related