CODEWITHSUNDEEP
phpmysqljoinleft-join
Nate
Nate

Reputation: 28384

Limiting a left join to returning one result?

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?

Upvotes: 21

Views: 118488

Answers (8)

QeiNui
QeiNui

Reputation: 65

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

Upvotes: 0

Sonu Chohan
Sonu Chohan

Reputation: 273

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

Upvotes: -2

Saghachi
Saghachi

Reputation: 945

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.

Upvotes: -2

oriadam
oriadam

Reputation: 8559

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

Upvotes: 2

Erik Olson
Erik Olson

Reputation: 567

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    
)

Upvotes: 11

i--
i--

Reputation: 4360

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

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270637

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

Upvotes: 42

Preet Sangha
Preet Sangha

Reputation: 65506

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

Upvotes: 1

Related Questions