yesterday yesterday - 11 days ago 8
MySQL Question

Selecting all courses + number of teachers

I have 3 tables

table: courses

course_id | slug
-------------------
1 | math
2 | science
3 | english

table: users

user_id | username
------------------
1 | john
2 | steve

table user_courses

user_course_id | user_id | course_id
------------------------------------
1 | 1 | 1
2 | 1 | 3
3 | 2 | 3


As you can see, the course math has only 1 teacher (john), but the course english has 2 teachers (john and steve).

I want to select all the courses from the table courses combined with the number of teachers for each course. How can I do this?

Thanks in advance!

Answer

Something like this?

SELECT Course.slug, Count(Users.user_id) as UserCount FROM Course 
    INNER JOIN User_Course ON User_Course.course_id = Course.course_id 
    INNER JOIN Users ON Users.user_id = User_Course.course_id
    GROUP BY Course.course_id;   

I would also have changed the logic of your user_course and used a composit primary key instead.

CREATE TABLE User_Course (
    user_id INT NOT NULL, 
    course_id INT NOT NULL, 
    PRIMARY KEY(user_id, course_id),
    CONSTRAINT user_course_user FOREIGN KEY (user_id) REFERENCES Users(user_id), 
    CONSTRAINT user_course_course FOREIGN KEY (course_id) REFERENCES Course(course_id)
); 

Here: SQLFiddel

Comments