teaflavored teaflavored - 2 months ago 15
SQL Question

SQL Join SQLzoo

There's a problem on SQLzoo under More Join operations for MySQL.
The problem asked is

List the films released in the year 1978 ordered by the number of actors in the cast.

The tables given are as follows, ord has value of 0 or 1, 1 for star role.

movie | actor | casting
id id movieid
yr name actorid
title ord
budget
gross
director


I wrote my SQL query as follows, SQLzoo isn't telling me I have the right answer, but I believe my query to be correct. Can someone verify this and possibly tell me how to do this without a subquery please? Thanks!

SELECT DISTINCT
movie.title, f.num_actors
FROM
(SELECT
casting.movieid,COUNT(casting.actorid) AS num_actors
FROM
casting
GROUP BY
casting.movieid ) f
JOIN
movie ON f.movieid = movie.id
WHERE
movie.yr = 1978
ORDER BY
2 DESC

CL. CL.
Answer

You can do the join first, then do the grouping:

SELECT movie.*
FROM movie
JOIN casting ON movie.id = casting.movieid
WHERE movie.yr = 1978
GROUP BY movie.id
ORDER BY COUNT(*) DESC