ZHANG KE ZHANG KE - 8 months ago 23
MySQL Question

In SQL database,how to use one column with different condition values pair with another column with different conditions values?

My database design is like image shows:
enter image description here

The data table is design for a survey's all respondent's answers. The query I need is, for example,

I want to know all RID(Respondent ID) that has conditions of QID = 2 and Answers = 26-35, and QID = 4 and Answers = "ASHFIELD". But the sql query below:

select * from RespondentAnswers
where (QID = 2 and Answers = '26-35') and (QID = 4 and Answers = 'ASHFIELD')


was obvious not correct.

In short, I want to know those respondents who is age from '26-35' and living in 'ASHFIELD'.

My database structure was showed in the image above. Any one has solution please? Thank you!

Answer

could be using a group by an having

select rid 
from RespondentAnswers
where (QID , Answers ) in ( (2, '26-35'),  (4,'ASHFIELD'))
group by rid 
having  count(*) =2;