Number of MySQL Rows with Three Tables

Question

My MySQL DB is as follows

**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 this conclusion:

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

Is there a way to do this in a single SQL query? And How?

Answer 1

 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 

Answer 2

 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