Select Multiple Values SQL

Question

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,

So are you trying to get the classroom numbers where both "Alfred" and "Maria" rows exist for that classroom? — ryanyuyu
– ryanyuyu, Commented Dec 30, 2014 at 16:59
Of course it doesn't work. There is no way a Person will be both 'Alfred' and 'Maria' on the same time. You want it be either 'Alfred' OR 'Maria'. — axiac
– axiac, Commented Dec 30, 2014 at 17:00
This is a legitimate (although very basic) question, wonder why the downvote? — user2366842
– user2366842, Commented Dec 30, 2014 at 17:03

Gordon Linoff · Accepted Answer · 2014-12-30 17:01:30Z

3

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;

answered Dec 30, 2014 at 17:01

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Kiki Over a year ago

After a fast test it seems to be working. Thanks a lot Gordon.

Pரதீப் · Accepted Answer · 2014-12-30 17:00:25Z

2

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

answered Dec 30, 2014 at 17:00

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

5 Comments

Gordon Linoff Over a year ago

Not quite right . . . The OP appears to want only those two names in the classroom.

Pரதீப் Over a year ago

@GordonLinoff - Sorry i didn't get you. Even my query is doing the same thing.

Gordon Linoff Over a year ago

. . Your query would pick up classroom = 2, because that has both the names, even though it also has Zoe.

Gordon Linoff Over a year ago

. . I fixed your SQL Fiddle to have the data in the OP to demonstrate the issue: sqlfiddle.com/#!2/754405. But in simple terms, the where is executed before the having, so other names are never considered in the having. Your query is a correct answer to a very similar question.

Pரதீப் Over a year ago

@GordonLinoff - Yea sir you are right. Thanks for making me understand

Collectives™ on Stack Overflow

Select Multiple Values SQL

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related