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

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

28

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

260,803
45
432
377

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
1

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

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

6

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

16,830
22
94
144

answered Aug 23 '19 at 12:27

Erik Olson

477
5
5

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

42,334
17
98
130

answered Jul 09 '12 at 02:37

i--

4,289
2
25
35

score 1 · Answer 4 · 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

62,844
17
138
209

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

1

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

6,735
2
43
44

score 0 · Answer 6 · answered Dec 28 '21 at 10:56

0

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

703
9
15

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. – DerpyNerd Feb 14 '22 at 09:05
@DerpyNerd I'm using this code on a project, and practically it's working great for me – Saghachi Feb 17 '22 at 07:25
1

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 :) – DerpyNerd Feb 17 '22 at 09:28

Limiting a left join to returning one result?

6 Answers6

Update after understanding the requirement better:

Linked