ZHANG KE ZHANG KE - 2 months ago 5
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;
Comments