I have 3 tables:

• students table

• classes table

• attendance table

I tried to write an SQL query to retrieve the students who attended both math and science classes:

SELECT s.id, s.name
FROM attendance a
INNER JOIN students s ON a.student_id = s.id
WHERE a.class_id IN (1,2);

the above code result is

But Emma only attended Math class.

I know this behavior is because of WHERE IN, it's the same as WHERE a.class_id = 1 OR a.class_id = 2.

But what should I do to get the expected results, Which is "Jhon" or only Jhon's ID because he is the only one who attended Math and Science Classes?

CodePudding user response：

Either join to attendance twice, once for each class:

SELECT s.id, s.name
FROM students s 
JOIN attendance a1 ON a1.student_id = s.id and a1.class_id = 1
JOIN attendance a2 ON a2.student_id = s.id and a2.class_id = 2

or join to both classes at once and use group by with having:

SELECT s.id, s.name
FROM students s 
JOIN attendance a ON a.student_id = s.id and class_id in (1, 2)
GROUP BY s.id, s.name
HAVING COUNT(*) = 2

CodePudding user response：

Using a CTE would help, and can help you extend to multiple classes. You are also missing a DISTINCT keyword, which is why Jhon appears twice. Maybe something like this:

WITH attendance_count(student_id, classes_attended)
AS (
 SELECT
    student_id,
    COUNT(id) AS classes_attended

    FROM attendance a 

    -- change this to change the classes
    WHERE a.class_id IN (1,2)

    GROUP BY student_id
)

SELECT DISTINCT s.id, s.name
    FROM attendance_count 
    INNER JOIN students s ON attendance_count.student_id = s.id

    -- if different number of classes, change this
    WHERE classes_attended = 2