[RESOLVED] Mysql, subquery, limit

Dmitriy

Hello.
A need help to make some query, but my brain seems to be asleep...

Table1 - category:
id name

Table2 - items:
id parent name

How can i get all categories + 5 items (not more) of EACH category?
Something like

SELECT a.*, b.* FROM category a
LEFT JOIN items b ON b.parent = a.id AND b.id IN (SELECT id FROM items WHERE parent = a.id LIMIT 5)

but it ofcourse not working...
Thanks in advance.

Derokorian

Try:

SELECT a.*, b.* FROM category a
LEFT JOIN (SELECT * FROM items WHERE parent = a.id LIMIT 5) b ON b.parent=a.id

I'm sure one of the guru's here will come up with some way of doing this I wouldn't even think of.

Edit: To do it your way, I think you need to change the AND to WHERE...

bradgrafelman

On the MySQL manual page entitled 12.2.9.9. Subquery Errors, there is a user comment that proposes one possible workaround for "the use of LIMIT in a subquery":

Frank Heikens on February 23 2008 5:01pm wrote:
There is a workaround for the use of LIMIT in a subquery, just use a variable (seperate query, execute this one first):
SET @i = 0;

And then the select-query with the subquery including the LIMIT:
SELECT
*
FROM
my_table
WHERE
id_my_other_table IN(
SELECT id FROM my_other_table
WHERE
( @i := ( @i +1 ) ) <= 10
);

In this case there's a limit of 10 results in the subquery.

Tested in version 5.0.45

[Link to comment]

Dmitriy

bradgrafelman, perfect solution! Thanks!

Derokorian, I've tried it, its not working.
And you were right about guru 🙂

Dmitriy

Result query looks like

select a.id, a.name, b.name, b.parent, @i := a.id 
from category a
left join items b on b.id in (select * from(select id from items where parent = @i limit 5) t)
order by a.id

May be that someone will need )

Derokorian

Interesting that you had to do a subquery of a subquery O_o

Dmitriy

Because "MySQL doesn't support 'LIMIT & IN/ALL/ANY/SOME subquery'".
At least, my version (5.1.54)

You can try some query, like

select name from some_table where id in (select id from table_name limit 5)

it will cause error.

Derokorian

Yeah I got that from brad's post, However what I meant was I'm surprised you had to do this:

select a.id, a.name, b.name, b.parent, @i := a.id 
from category a
left join items b on b.id in (select * from(select id from items where parent = @i limit 5) t)
order by a.id

Instead of this:

select a.id, a.name, b.name, b.parent, @i := a.id 
from category a
left join items b on b.id in (select id from items where parent = @i limit 5)
order by a.id

However looking at it now I'm still surprised it works, because your "working" query still has a limit in a subquery O_o

Weedpacket

Wow. MySQL has been around how long and it still can't get subqueries right? Why do people still use it?

Dmitriy

Derokorian, all problems due to operator IN.
All subqueries like IN(.... limit [N]) breaks with error.

Weedpacket, error says 'This version of MySQL doesn't yet support'.
May be last version cat it, but i cant update servers of hosters 🙂

Weedpacket

Dmitriy wrote:
May be last version cat it, but i cant update servers of hosters

I had a look. It doesn't.