Subqueries in FROM clause


SELECT  students.name AS student,
        courseSummary.courseName AS course,
        courseSummary.numberOfBooks AS numberOfBooks
FROM    students JOIN (
                        SELECT   courses.id AS courseId,
                                 courses.name AS courseName,
                                 COUNT(books.id) AS numberOfBooks
                        FROM     courses LEFT JOIN books
                        ON       books.courseId = courses.id
                        GROUP BY courses.Id
                      ) AS courseSummary
ON      students.courseId = courseSummary.courseId
