Limiting a left join to returning one result?

php mysql join left-join

73,724

Solution 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

Solution 2

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    
)

Solution 3

you could try to add GROUP BY t3.movie_id to the first query

Solution 4

Try this:

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

Solution 5

On MySQL 5.7+ use ANY_VALUE & GROUP_BY:

SELECT t1.id,t1.movie_name, ANY_VALUE(t3.popularity) popularity
FROM t1
LEFT JOIN t3 ON (t3.movie_id=t1.movie_id AND t3.popularity=0)
GROUP BY t1.id

more info LEFT JOIN only first row

https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

View more solutions

73,724

Author by

Nate

I'm a senior in college, majoring in EE and minoring in CS, with a passion for electronics and programming. I'm an entrepreneur and started a small hobby electronics company called FoxyTronics a few years ago, and am now working on launching a shopping website called PriceWombat.

Updated on December 29, 2021

Comments

Nate over 2 years
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?
Nate almost 12 years

Thanks a lot for your help. I rewrote the query to make it as in your example and it almost is working, but not quite. If I remove LIMIT 1 from the statement, then it returns multiple results (movie names) as before, but if I leave LIMIT 1 in the query then no movie names are returned. Any idea why that would be? Thanks again.
Michael Berkowski almost 12 years

@Nate That's a bit weird. I put in an explicit ORDER BY (which should have been there) but I don't think that's the reason.
Michael Berkowski almost 12 years

@Nate Run the subquery by itself and verify that it returns the correct row with the WHERE clause you have.
Michael Berkowski almost 12 years

@Nate and what's your MySQL version? Older versions did not support LIMIT in subqueries, though it works since 5.0 at least.
Nate almost 12 years

I see what's happening. It's returning the movie name (in my example) for the first row in the table, not for the current movie. I.e. if I add AND movie_id = 2 after WHERE popularity = 0 then it returns the movie name for that id. I tried adding AND movie_id = t1.movie_id, but that dies with the error Unknown column 't1.movie_id' in 'where clause'. Hmm..
Michael Berkowski almost 12 years

@Nate the movie_id = t1.movie_id is covered by the join ON clause. I would need to see your full query to diagnose further.
Nate almost 12 years

Michael, won't the query: SELECT movie_id, movie_name FROM movies WHERE popularity = 0 ORDER BY movie_name LIMIT 1 just retrieve the first row in the table movies (ordered by movie_name)? I think that's what's happening..
Michael Berkowski almost 12 years

@Nate Yes, and I appear to have misunderstood your requirement entirely. If you want only one per group to join against, you'll need a max() aggregate. Stay tuned for a minute and I'll update...
Nate almost 12 years

You sir, are a genius! It works beautifully! I suppose now I should try to actually understand how it works :-) Thanks!
DerpyNerd about 2 years

I'm no expert, but table B inside a subquery of the join statement for table B doesn't exist yet becuase the join itself hasn't been fully executed yet.
Saghachi about 2 years

@DerpyNerd I'm using this code on a project, and practically it's working great for me
DerpyNerd about 2 years

Hi Saghachi, you're absolutely right, it works. My bad! Since I wasn't able to decipher this simple query, I'll close my laptop and facepalm myself to sleep :)