'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 |
|---|
