CODEWITHSUNDEEP
sqlsqlite
Nelluk
Nelluk

Reputation: 1241

Select record that has specific records related, but no additional records related

For a many to many relationship, for example Classes and Students, I'd like to select all Classes with exactly a given membership.

Students         Classes        StudentClass
=========       ============    =================
id              id              student     class
--              ---             --------   -------
1               1               1           1
2               2               2           1
3                               3           1
4                               1           2
5                               2           2             
                                3           2
                                4           2

With example data, If I give the query S1, S2, S3 - it should return Class 1 only, and exclude Class 4 because it contains an additional student.

Upvotes: 3

Views: 55

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

SQL HERE

You can do it like below :

select class from StudentClass
where class in(
    select class from StudentClass where student in(1,2,3) 
    group by class having count(distinct student)=3
) group by class
having count(distinct student)=3

Upvotes: 1

sgeddes
sgeddes

Reputation: 62841

Here's one option using conditional aggregation:

select class
from studentclass
group by class
having count(case when student in (1,2,3) then 1 end) = 3
   and count(*) = 3

Upvotes: 3

Related Questions