CODEWITHSUNDEEP
phpmysqlsqlselect
Kiki
Kiki

Reputation: 75

Select Multiple Values SQL

I have a table where the data are as follow

ID Classroom Person
1   1         Alfred
2   1         Maria
3   2         Maria
4   2         Zoe
5   2         Alfred
6   3         Nick
7   3         Paul
8   3         Mike
9   3         Alfred
10  4         Zoe
11  4         Maria

I want to select and return only the Classroom that has as Person only 'Alfred' and 'Maria' Following statement : 

Select * from table_name where (Person='maria') and (Person=Alfred')

doesn't seem to work.

You can see a SQL Fiddle here,

Upvotes: 1

Views: 73

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can use group by and having:

select classroom
from table t
group by classroom
having count(*) = 2 and
       sum(person in ('maria', 'Alfred')) = 2;

This assumes that one person cannot be in a classroom multiple times.

This checks that there are two names in the classroom and they are for the two names of interest. If you can have duplicates, you would want:

having count(distinct name) = 2 and
       count(distinct case when person in ('maria', 'Alfred') then person end) = 2;

Upvotes: 3

Pரதீப்
Pரதீப்

Reputation: 93694

Try this. Group by and having with Count should work.

SELECT Classroom
FROM   tablename
WHERE  Person IN( 'maria', 'Alfred' )
GROUP  BY classroom
HAVING Count(Person) = 2

Upvotes: 2

Related Questions