CODEWITHSUNDEEP
mysql
Mansoor Ahmed
Mansoor Ahmed

Reputation: 83

how to get single id in many to many relationship in mysql

I have a student_class table that is mapping student with classes. I want to get student_id from this table with class ids as input. e.g:

https://i.sstatic.net/r2l0u.jpg

Now I want a mysql query in which I will pass class_ids 3,5 and 9 and it should return me student_id 1.???

Upvotes: 1

Views: 121

Answers (2)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

Here is another mysql specific approach

select student_id
from student_class
where class_id in (3, 5, 9)
group by student_id
having sum(class_id = 3) > 0
and sum(class_id = 5) > 0
and sum(class_id = 9) > 0

Upvotes: 0

trincot
trincot

Reputation: 350270

You could do it with having:

 select   student_id
 from     student_class
 where    class_id in (3, 5, 9)
 group by student_id
 having   count(distinct class_id) = 3

Upvotes: 1

Related Questions