Search code examples
phpsqlmysqlcountrowcount

Row count MySQL with three tables

My MySQL DB looks like this

**table_schools**
id | name
1  | school_1
2  | school_2

**table_classes**
id | class | school_id
1  | a     | 1
2  | b     | 1
3  | c     | 2
4  | d     | 2
5  | e     | 2

**table_students**
id | name  | class_id
1  | Nick  | 1
2  | Tom   | 2
3  | Kevin | 3
4  | Jane  | 4
5  | Mark  | 5
6  | Tim   | 5
7  | Lynn  | 5

I would like to have an output like this:

school_name | class_count | student_count
school_1    | 2           | 2
school_2    | 3           | 5

Is there a way to do this in ONE sql query? And how?

Solution 
  • SELECT  s.name, COUNT(DISTINCT c.id) AS classes, COUNT(st.id) AS students
FROM    table_schools s
LEFT JOIN
        table_classes c
ON      c.school_id = s.id
LEFT JOIN
        table_students st
ON      st.class_id = c.id
GROUP BY
        s.id