CODEWITHSUNDEEP
mysqljoincount
Jasper
Jasper

Reputation: 555

MySQL COUNT() with multiple joins

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!

Upvotes: 1

Views: 1652

Answers (1)

Edper
Edper

Reputation: 9322

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

Upvotes: 3

Related Questions