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 | 开发者_StackOverflow社区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?
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
SELECT table_schools.name, COUNT(table_classes.name) AS classes, COUNT(table_students.id) AS students
FROM table_schools
LEFT JOIN table_classes ON table_schools.id = table_classes.school_id
LEFT JOIN table_students ON table_students.class_id = table_classes.id
GROUP BY table_schools.id, table_classes.id
ORDER BY table_schools.name
精彩评论