'Referencing column from outer-subquery in inner subquery where clause

I am pretty new to sql in general and I am running into some problems with a subquery in mysql. The subquery is:

SELECT b.movie_id, title, summary, rating, num_ratings, freq, ct FROM
        (SELECT a.movie_id, freq, (SELECT count(*) from ((SELECT genre_name from movies.genre_table where movie_id = '21-jump-street') as x
            INNER JOIN (SELECT genre_name from movies.genre_table where movie_id = a.movie_id) as y on 
                x.genre_name = y.genre_name)) as ct
            FROM
                (SELECT movie_id, count(movie_id) as freq
                    from movies.user_movies 
                    WHERE user in
                        (SELECT user 
                            FROM movies.user_movies
                            WHERE movies.user_movies.movie_id = '21-jump-street' AND rating >= 4.5)
                        AND  rating >= 4.5 AND movie_id != '21-jump-street' 
                    GROUP BY movie_id) AS a)
            as b
        INNER JOIN movies.movie_table on b.movie_id = movie_table.movie_id
        ORDER BY count DESC, freq DESC

My table schema is:

genre_table has movie_id and genre_name, 

movie_table has movie_id, title, summary, ratings, and num_ratings, 

user_movies has user, movie_id, and rating.

The goal of this query is to select all of the highly rated movies from user_movies that users who rated the input movie (21 jump street in this case) highly. Then it should compare the genres of the input movie to each movie to generate a count of matched genres. Finally the query returns the recommended movies descending by matching the most genres and then by the frequency of times rated highly.

However, when I run this, I receive

Error Code 1054: Unknown column 'a.movie_id' in 'where clause'.

I know you can't generally put subquery results in where clauses, because the where can be evaluated first. Is there anything I can do about this or a way I can change the query to get the desired result?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source