[FIXED] Limiting a left join to returning one result?

Issue

I currently have this left join as part of a query:

LEFT JOIN movies t3 ON t1.movie_id = t3.movie_id AND t3.popularity = 0

The trouble is that if there are several movies with the same name and same popularity (don't ask, it just is that way :-) ) then duplicate results are returned.

All that to say, I would like to limit the result of the left join to one.

I tried this:

LEFT JOIN 
    (SELECT t3.movie_name FROM movies t3 WHERE t3.popularity = 0 LIMIT 1)
     ON t1.movie_id = t3.movie_id AND t3.popularity = 0

The second query dies with the error:

Every derived table must have its own alias

I know what I'm asking is slightly vague since I'm not providing the full query, but is what I'm asking generally possible?

Solution

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

Answered By - Michael Berkowski