Question 291642 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to tune simple sql query with group by

I have three tables

1.) Students (id,firstName,Surname,email)

2.) Course_Student(student_id, course_id)

3.) Courses (id, name, classroom_id)

I need to select all students, which have count of curses more or equal = 3.

I contstruct this select and it seems that it works, but I am curious, if there will be more efective way to this select:

select 
  s.firstName, s.Surname 
from 
  students s  
inner join 
  course_student cs 
on 
  s.id = cs.student_id 
and 
  cs.course_id 
in 
  (select 
     course_id 
   from 
     course_student 
   group by 
     course_id 
   having count(*) >= 3
  );
Tags:

2

Answers


  1. 0

    You can make every IN clause into an INNER JOIN

    select 
  s.firstName, s.Surname 
from 
  students s  
inner join 
  course_student cs 
on 
  s.id = cs.student_id 
JOIN   (select 
     course_id 
   from 
     course_student 
   group by 
     course_id 
   having count(*) >= 3
  ) c ON c.course_id =  cs.course_id
    Login or Signup to reply.
  2. 0

    Your current query selects all students who are on a course which has >= 3 students, but maybe that is what you want.

    This query "select all students, which have count of curses more or equal = 3":

    SELECT s.firstName, s.Surname
FROM students s
INNER JOIN course_student cs ON s.id = cs.student_id
GROUP BY s.id
HAVING COUNT(*) >= 3;

    Make sure you index your junction table in both directions – (course_id, student_id) & (student_id, course_id).

    Here’s a db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search