Limiting a left join to returning one result?

Question

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?

Buyse

that is the last left join, right? you're not going LEFT JOIN ... ON ... AND ... LEFT JOIN ... are you? and is LIMIT 1 at the end of your statement not what you're after? – Meanwhile

Have a look at the DISTINCTstatement, maybe that can solve your problem. – Steffaniesteffen

@jared - it is the last LEFT JOIN in the query, yes. There are two more before it. I can't just stick LIMIT 1 at the end, however, because the query as a whole returns many rows. – Buyse

Does this answer your question? MySQL JOIN the most recent row only? – Leavis

Does this answer your question? Fetch the row which has the Max value for a column – Column

Answer 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

Polyp

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. – Buyse

@Buyse 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. – Polyp

@Buyse Run the subquery by itself and verify that it returns the correct row with the WHERE clause you have. – Polyp

@Buyse and what's your MySQL version? Older versions did not support LIMIT in subqueries, though it works since 5.0 at least. – Polyp

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.. – Buyse

@Buyse the movie_id = t1.movie_id is covered by the join ON clause. I would need to see your full query to diagnose further. – Polyp

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.. – Buyse

@Buyse 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... – Polyp

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

Thanks so much, doing the bracket select at start of left join was what I needed. – Neoclassic

does this have any performance implication as compared to a simple left join? – Collado

Answer 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    
)

Kriskrischer

Answer 3

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

Morrow

Answer 4

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

February

Answer 5

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

Many

Answer 6

LEFT JOIN (
  SELECT id,movie_name FROM movies GROUP BY id
) as m ON (
   m.id = x.id 
)

Pantomimist

Answer 7

Easy solution to left join the 1 most/least recent row is using select over ON phrase

SELECT A.ID, A.Name, B.Content
FROM A
LEFT JOIN B
ON A.id = (SELECT MAX(id) FROM B WHERE id = A.id)

Where A.id is the auto-incremental primary key.

Leavis

you can't use A inside a subquery, postgres ``` ERROR: invalid reference to FROM-clause entry for table "A" SQL state: 42P01 Hint: There is an entry for table "A", but it cannot be referenced from this part of the query.``` – Paniculate

Answer 8

// Mysql

SELECT SUM(db.item_sales_nsv) as total FROM app_product_hqsales_otc as db 
LEFT JOIN app_item_target_otc as it ON 
db.id = (SELECT MAX(id) FROM app_item_target_otc  as ot WHERE id = db.id) 
and db.head_quarter = it.hqcode 
AND db.aaina_item_code = it.aaina_item_code AND db.month = it.month 
AND db.year = it.year
WHERE db.head_quarter = 'WIN001' AND db.month = '5' AND db.year = '2022' AND db.status = '1' 

Tipperary