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?

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? — jay, Jul 09 '12 at 02:29
Have a look at the `DISTINCT`statement, maybe that can solve your problem. — golja, Jul 09 '12 at 02:31
@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. — Nate, Jul 09 '12 at 02:34
Does this answer your question? [MySQL JOIN the most recent row only?](https://stackoverflow.com/questions/3619030/mysql-join-the-most-recent-row-only) — Saghachi, Dec 28 '21 at 18:32
Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — philipxy, Jun 07 '22 at 13:22

score 36 · Accepted Answer · edited Jun 20 '20 at 09:12

36

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Jul 09 '12 at 02:30

Michael Berkowski

267,341
46
444
390

1

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. – Nate Jul 09 '12 at 02:45
1

@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 Jul 09 '12 at 02:49
1

@Nate Run the subquery by itself and verify that it returns the correct row with the WHERE clause you have. – Michael Berkowski Jul 09 '12 at 02:52
1

@Nate and what's your MySQL version? Older versions did not support `LIMIT` in subqueries, though it works since 5.0 at least. – Michael Berkowski Jul 09 '12 at 02:53
1

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.. – Nate Jul 09 '12 at 03:00
1

@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. – Michael Berkowski Jul 09 '12 at 03:04
1

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.. – Nate Jul 09 '12 at 03:12
1

@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... – Michael Berkowski Jul 09 '12 at 03:16
2

You sir, are a genius! It works beautifully! I suppose now I should try to actually understand how it works :-) Thanks! – Nate Jul 09 '12 at 03:25
Thanks so much, doing the bracket select at start of left join was what I needed. – James Osguthorpe Nov 28 '22 at 15:48
does this have any performance implication as compared to a simple left join? – albanx Jul 15 '23 at 08:33

score 10 · Answer 2 · edited Aug 23 '19 at 15:20

10

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    
)

edited Aug 23 '19 at 15:20

Val

17,336
23
95
144

answered Aug 23 '19 at 12:27

Erik Olson

547
5
6

score 2 · Answer 3 · edited Oct 23 '15 at 12:58

2

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

edited Oct 23 '15 at 12:58

fubo

44,811
17
103
137

answered Jul 09 '12 at 02:37

i--

4,349
2
27
35

oriadam · Answer 4 · 2021-10-10T06:41:43.540

2

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

edited Oct 10 '21 at 06:41

answered Dec 22 '20 at 11:10

oriadam

7,747
2
50
48

score 1 · Answer 5 · answered Jul 09 '12 at 02:30

1

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

answered Jul 09 '12 at 02:30

Preet Sangha

64,563
18
145
216

score 0 · Answer 6 · answered Jul 13 '22 at 02:21

0

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

answered Jul 13 '22 at 02:21

QeiNui

65
1
8

score -1 · Answer 7 · answered Dec 28 '21 at 10:56

-1

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.

answered Dec 28 '21 at 10:56

Saghachi

851
11
19

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.``` – FiruzzZ Mar 21 '23 at 16:45

score -2 · Answer 8 · answered Jun 07 '22 at 10:55

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

Limiting a left join to returning one result?

8 Answers8

Update after understanding the requirement better:

Linked