Saibamen Saibamen - 1 year ago 55
MySQL Question

How to select records that contains IDs but not contains only IDs from this same list

I want to have selected records that contains selected IDs but doesn't contains only these IDs from this same lists.

My SQL code:

select question_id, person_id from `answers` where
`person_id` in ('9', '18')


2, 9
2, 18
4, 9
4, 18
5, 18
6, 9

Expected results:

5, 18
6, 9

Full SQL query:

select id, name
from questions
where id not in ('3', '13') and
exists (select `id` from `answers` where `answers`.`question_id` = `questions`.`id`
and `person_id` in ('9', '18')) order by RAND() limit 1

Answer Source

You could try using group by and having

select question_id, person_id 
from `answers` 
where person_id` in ('9', '18')
group by question_id 
having count(distinct person_id) =  1

selecting only the rows with person_id in your set but that match only one value

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download