MySQL COUNT() with multiple joins

Question

I am building a course system, with courses, instructors and a table to relate the two.

Symplified table instructors:

id | name | ...

Symplified table courses:

id | name | instructors_needed | ...

Symplified table link:

id | course_id | instructor_id

I created the following query to fetch the names of the instructors associated to a particular course:

SELECT i.name, c.name, c.instructors_needed FROM courses c
LEFT OUTER JOIN
    link
ON c.id = link.course_id
LEFT OUTER JOIN
    instructors i
ON link.instructor_id = i.id

This works fine. I created the following query to find the number of instructors on each course:

SELECT COUNT(i.name) as number, c.id, c.name, c.instructors_needed FROM courses c
LEFT OUTER JOIN
    link
ON c.id = link.course_id
LEFT OUTER JOIN
    instructors i
ON link.instructor_id = i.id
GROUP BY c.ID

I want to combine the two queries, to get all details about the instructors for a particular case, but also the total number of instructors on the course and the number of instructors needed. How do I do that? I understand that the GROUP BY is the problem here. I searched but I could only find examples with 2 tables instead of 3, and I somehow can't figure it out for three tables.

Your help is really appreciated, thank you!

rule of the thumb: whenever you come into the need of using un-aggregated fields ( aka columns not used with summing up functions like, count/sum/average ) and aggregated ones, list all the un-aggregated fields in the GROUP-BY statement. So you most likely only need to write GROUP BY c.ID, c.name, c.instructors_needed ( this is of course alot "dumbed down" and GROUP BY has several good other uses espec together with HAVING) — Najzero
– Najzero, Commented Jun 14, 2013 at 11:47
Hello Najzero, thanks for responding! I tried your suggestion but this gives me a maximum of 1 instructor name for each course. But @Edper 's solution did the trick. — Jasper
– Jasper, Commented Jun 14, 2013 at 18:01

Edper · Accepted Answer · 2017-07-30 23:57:44Z

3

Try:

SELECT i.name, c.name, c.instructors_needed, Ctr.CourseCount FROM courses c
LEFT OUTER JOIN
 link
ON c.id = link.course_id
LEFT OUTER JOIN
  instructors i
ON link.instructor_id = i.id
LEFT OUTER JOIN
  (SELECT link.course_id, COUNT(*) as CourseCount FROM link GROUP BY link.course_id) Ctr
ON link.course_id = Ctr.course_id

edited Jul 30, 2017 at 23:57

answered Jun 14, 2013 at 11:57

Edper

9,3621 gold badge32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL COUNT() with multiple joins

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related