find the common Class ID for a list of students

Question

I try to get the class id from student and class_student tables using jpql.

I give a list of students of students like this ('name1', 'name2', 'name5'), then if this list was assign to class, then I will have the class ID (class id = 2).

Note: the list ist unique, that means this list is only in one class.

CREATE DATABASE test;

USE test;

CREATE TABLE CLASS
(   ID   BIGINT       NOT NULL PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL
)    CHARSET = utf8mb4;

CREATE TABLE STUDENT
(   ID   BIGINT       NOT NULL PRIMARY KEY,
    NAME VARCHAR(255) NOT NULL,
    constraint UK_NAME
        unique (NAME)
)CHARSET = utf8mb4;


CREATE TABLE CLASS_STUDENT
(ID         BIGINT NOT NULL PRIMARY KEY,
    CLASS_ID   BIGINT NOT NULL,
    STUDENT_ID BIGINT NOT NULL,

    CONSTRAINT FK_STUDENT
        FOREIGN KEY (STUDENT_ID) REFERENCES STUDENT (ID),

    CONSTRAINT FK_CLASS
        FOREIGN KEY (CLASS_ID) REFERENCES CLASS (ID)
)CHARSET = utf8mb4;

INSERT INTO CLASS (ID, name)
VALUES (1, 'class1'),
       (2, 'class2'),
       (3, 'class3');

INSERT INTO STUDENT (ID, name)
VALUES (1, 'name1'),
       (2, 'name2'),
       (3, 'name3'),
       (4, 'name4'),
       (5, 'name5');

INSERT INTO CLASS_STUDENT (ID, CLASS_ID, STUDENT_ID)
VALUES (1, 1, 1),
       (2, 1, 2),
       (3, 1, 3),
       (4, 1, 4),
       (5, 2, 1),
       (6, 2, 3),
       (7, 2, 5),
       (8, 3, 1),
       (9, 3, 3),
       (10, 3, 4),
       (11, 3, 5);

Answer 1

This is a relational division problem. You can use aggregation and a having clause for filtering.

select cs.class_id
from class_student cs
inner join student s on s.id = cs.student_id
where s.name in ('name1', 'name2', 'name3')
group by cs.class_id
having count(*) = 3

The join brings in the students of each class, and the where clause filters on the three students we are interested in. Then, we group by class, and retain groups that contain three rows (one per student).

---

Edit:

If you want classes that have these three students and no other, then:

select cs.class_id
from class_student cs
inner join student s on s.id = cs.student_id
where s.name in ('name1', 'name2', 'name3')
group by cs.class_id
having sum(case when s.name     in ('name1', 'name2', 'name3') then 1 else 0 end) = 3
   and sum(case when s.name not in ('name1', 'name2', 'name3') then 1 else 0 end) = 0