MySql - apply limit to joined rows only

Question

Consider the following query:

SELECT u.id
     , u.name
     , p.id
  FROM users u
  LEFT 
  JOIN posts p
    ON p.id IN(
SELECT x.id 
  FROM posts x 
 WHERE x.user_id = u.id
ORDER 
    BY x.featured DESC 
LIMIT 10
);

I am trying to join the posts table to the users table. However I only want to retrieve a maximum of 10 posts per user.

This approach throws the following error:

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Is there an alternative approach to achieve the desired result?

Whenever possible, take a moment to analyze your queries. When written correctly, subqueries can greatly reduce the amount of overhead and execution time. Since you are using an older MySQL version I believe you can `set profiling = 1;`, `enter query here;` `show profiles;`. from the cmd prompt or MySQL Workbench. You can use the `DESCRIBE` keyword or `SHOW PROCESSLIST` too. — yardpenalty.com, Feb 10 '19 at 21:16
Possible duplicate of [MySQL select top X records for each individual in table](https://stackoverflow.com/questions/9969126/mysql-select-top-x-records-for-each-individual-in-table) — Nick, Feb 10 '19 at 23:41
Possible duplicate of [MySQL - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery](https://stackoverflow.com/questions/17892762/mysql-this-version-of-mysql-doesnt-yet-support-limit-in-all-any-some-subqu) — xerx593, Feb 11 '19 at 00:58

ScaisEdge · Answer 1 · 2019-02-11T06:08:05.970

0

You should use an inner join on subquery instead of left join and IN clause

SELECT users.id, users.name, t.id
FROM users
INNER JOIN (
SELECT posts.id, posts.user_id
FROM posts 
WHERE posts.user_id = users.id
ORDER BY posts.featured DESC 
LIMIT 10 ) t on t.user_id  = users.id

In this way you don't use a limit inside a In clause for subquery and this should not raise the error .. the error for both LIMIT & IN .... but not for athe sue of in limit in subquery in JOIN (withou IN clause)

edited Feb 11 '19 at 06:08

answered Feb 10 '19 at 20:44

ScaisEdge

131,976
10
91
107

This is a better approach but he also needs an alternative to LIMIT – yardpenalty.com Feb 10 '19 at 20:47
1

@yardpenalty This should work because the LIMIT is not used inside a subquery for IN clause ..the error message if for "LIMIT & IN ...." – ScaisEdge Feb 10 '19 at 20:52
[scaisEdge](https://stackoverflow.com/users/3522312/scaisedge) Gotcha! I didn't look closely enough...Maybe edit your answer on how this eliminates his limitation on the ability to use the `LIMIT` with `IN/ALL/ANY/SOME`. Well done sir. – yardpenalty.com Feb 10 '19 at 21:01

MySql - apply limit to joined rows only

1 Answers1