Mysql select row having only specified values in a column

Question

Hi I have this mysql table named student_subject_table:

+----+-------------+---------------------+ 
| id | student_id  | subject             | 
+----+-------------+---------------------+ 
|  1 |           7 | math                | 
|  2 |           7 | physics             |  
|  3 |           7 | chemistry           |  
|  8 |           8 | math                | 
|  9 |           8 | physics             | 
| 10 |           8 | chemistry           | 
| 11 |           8 | biology             |  
+----+-------------+---------------------+

I am trying to get the ids of student those who have only math, physics and chemistry as subject.

I tried in clause:

 select student_id
 from student_subject table
 where
   subject in ('math', 'physics', 'chemistry')
 GROUP BY (student_id)

As expected I am getting both 7 and 8 as student_id. In the above table I need only student_id = 7.

Is there any way in mysql I can do this ?

Please help.

fthiella · Accepted Answer · 2015-12-08 15:28:51Z

2

You could use this query:

select student_id
from student_subject table
group by student_id
having
  SUM(subject IN ('math', 'physics', 'chemistry')) = COUNT(DISTINCT subject)
  AND COUNT(DISTINCT subject)=3

SUM(subject IN ('math', 'physics', 'chemistry')) will be 3 for both student 7 and 8
COUNT(DISTINCT subject) will be 3 for student 7 and 4 for student 8

if (student_id, subject) is unique, you can replace COUNT(DISTINCT ...) with COUNT(*).

only student 7 will be returned.

answered Dec 8, 2015 at 15:28

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Comments

Abhijeet · Accepted Answer · 2015-12-08 16:00:56Z

0

Use DISTINCT Clause

SELECT DISTINCT student_id 
FROM student_subject_table 
WHERE subject IN ('math', 'physics', 'chemistry') 
GROUP BY (student_id);

edited Dec 8, 2015 at 16:00

answered Dec 8, 2015 at 15:35

Abhijeet

411 gold badge1 silver badge7 bronze badges

Collectives™ on Stack Overflow

Mysql select row having only specified values in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related