Search code examples
mysqlgroup-byhaving

Select COUNT from different tables

Let's say I have two related tables: teacher and Course meaning that a Teacher can teach in many Courses but one Course can only have a Teacher at a time so, I create this two tables like this:

create table teacher (
   id INT NOT NULL UNIQUE,
   name VARCHAR(50) NOT NULL,
   lastname VARCHAR(50) NOT NULL
);

create table Course (
   id INT NOT NULL UNIQUE,
   name VARCHAR(50) NOT NULL,
   teacher_id INT NOT NULL
);

So, what I have for instance, is something like this 

| id | name     | lastname  | 
-----------------------------
| 1  | teacher1 | lastname1 |
| 2  | teacher2 | lastname2 |

| id | name     | teacher_id |
------------------------------
| 1  | course1  | 1          |
| 2  | course2  | 1          |
| 3  | course4  | 2          |

So far everything works ok but, by requirement I need to return a list of teachers and how many courses teachs like:

| id | name     | lastname   | courses  |
----------------------------------------
| 1  | teacher1 | lastname1  |  2       |
| 2  | teacher2 | lastname2  |  1       |

I can't find any solution that can adjust to this requirement. I tried with HAVING and GROUP BY clauses from MySql but that doesn't seem to work as I need.

Solution

  • You can use a left join using the teacher table as the left table. It will select all teachers from the teacher table and you can retrieve their courses id. If the teacher has no course it still returns the teacher record but with a null course Id. From there you can do a distinct count on the course id. See below:

    select a.id,
a.name,
a.lastname,
count(distinct b.id) as courses
from teacher a
left join courses b on a.id=b.teacher_id
group by a.id,
a.name,
a.last_name;

    Here is an image illustrating the different types of joins https://www.google.fr/amp/s/amp.reddit.com/r/programming/comments/1xlqeu/sql_joins_explained_xpost_rsql/