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.

    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...

      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]

        bradgrafelman, perfect solution! Thanks!

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

          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 )

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

              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.

                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

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

                    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 🙂

                      Dmitriy wrote:

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

                      I had a look. It doesn't.

                        Write a Reply...