Why is this SQL query failing?

Question

Consider the following SQL:

SELECT mov
FROM movie
WHERE mov IN
    (
        SELECT mov
        FROM movie_star
        GROUP BY(mov)
        HAVING count(star) > 6
    )
INNER JOIN movie_star
ON movie_star.mov = movie.mov;

I am getting the following error when this query is executed:

.ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'inner join movie_star on movie_star.mov = movie.mov' at line 3

Answer 1

The JOIN has to go before the WHERE clause.

SELECT movie.mov
FROM movie
INNER JOIN movie_star ON movie_star.mov = movie.mov
WHERE mov IN (SELECT mov FROM movie_star GROUP BY mov HAVING COUNT(star) > 6);

Answer 2

Here's the same query cleaned up a little:

select mov
from movie
where
    mov in (select mov from movie_star group by(mov) having count(star) > 6)
inner join movie_star on movie_star.mov = movie.mov

You have a syntax error because an inner join appears after your where clause, when it should appear immediately after the from clause instead.

Try this instead:

select mov
from movie
inner join movie_star on movie_star.mov = movie.mov
group by mov
having count(*) > 6