CODEWITHSUNDEEP
sql
Alex
Alex

Reputation: 2181

SQL: How to filter a set in join operation?

I have 3 tables.

CREATE TABLE students (
  student_id   INT         NOT NULL,
  student_name VARCHAR(10) NOT NULL,
  PRIMARY KEY (student_id)
);

CREATE TABLE courses (
  course_id   INT         NOT NULL,
  course_name VARCHAR(10) NOT NULL,
  PRIMARY KEY (course_id)
);

CREATE TABLE student_courses (
  student_id INT NOT NULL,
  course_id  INT
);

I want to get all students that assigned to more than 2 courses. Here is my try:

SELECT
  s.student_name,
  c.course_name
FROM students s
  JOIN student_courses sc
    ON s.student_id = sc.student_id
  JOIN courses c
    ON c.course_id = sc.course_id
WHERE s.student_id
  IN (SELECT s.student_id
      FROM students s
        JOIN student_courses sci
          ON s.student_id = sci.student_id
        JOIN courses ci
          ON ci.course_id = sci.course_id
      GROUP BY s.student_id
      HAVING COUNT(s.student_id) > 2);

It works. But I think it should be a simpler way to do it.

Upvotes: 1

Views: 40

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Technically this will suffice:

SELECT student_id FROM student_courses GROUP BY student_id HAVING count(*) > 2

It returns the student ID for any student assigned to more than 2 courses

If you want more info about the student, you can alias this query and join additional tables to it

SELECT ... FROM students s INNER JOIN
(SELECT student_id FROM student_courses GROUP BY student_id HAVING count(*) > 2) a ON a.student_id = s.id

Note that your spec is at loggerheads with your "it works" code, because that code says HAVING count(*) > 1

"Try and avoid using IN on lists any longer than those you'd be willing to write by hand" is a rule of thumb I've always sought to implement

Upvotes: 1

Related Questions