Limiting a left join to returning one result?
LEFT JOIN movies as m ON m.id = (
SELECT id FROM movies mm WHERE mm.movie_id = t1.movie_id
ORDER BY mm.id DESC
LIMIT 1
)
The error is clear -- you just need to create an alias for the subquery following its closing )
and use it in your ON
clause since every table, derived or real, must have its own identifier. Then, you'll need to include movie_id
in the subquery's select list to be able to join on it. Since the subquery already includes WHERE popularity = 0
, you don't need to include it in the join's ON
clause.
LEFT JOIN (
SELECT
movie_id,
movie_name
FROM movies
WHERE popularity = 0
ORDER BY movie_name
LIMIT 1
) the_alias ON t1.movie_id = the_alias.movie_id
If you are using one of these columns in the outer SELECT
, reference it via the_alias.movie_name
for example.
Update after understanding the requirement better:
To get one per group to join against, you can use an aggregate MAX()
or MIN()
on the movie_id
and group it in the subquery. No subquery LIMIT
is then necessary -- you'll receive the first movie_id
per name withMIN()
or the last with MAX()
.
LEFT JOIN (
SELECT
movie_name,
MIN(movie_id) AS movie_id
FROM movies
WHERE popularity = 0
GROUP BY movie_name
) the_alias ON t1.movie_id = the_alias.movie_id