CODEWITHSUNDEEP
mysqljoinlimit
ahoo
ahoo

Reputation: 1361

Join with limit on right table

Say I have two tables I want to join. Categories:

id   name
----------
1    Cars
2    Games
3    Pencils
4    Books

And items:

id   categoryid   itemname
---------------------------
1    1            Ford
2    1            BMW
3    1            VW
4    2            Tetris
5    2            Pong
6    3            Foobar Pencil Factory

I want a query that returns the category and the last maximum N (for example: 2) itemname:

category.id category.name item.id item.itemname
-------------------------------------------------
1           Cars          2       BMW
1           Cars          3       VW
2           Games         4       Tetris
2           Games         5       Pong
3           Pencils       6       Foobar Pencil Factory
4           Books         NULL    NULL

I want write a query like below:

Select * From categories c
 Left Join (select * from items order by id desc) i
 On c.id=i=categoryid
  AND LIMIT 2 #comment: N=2 this line not supported
 Where i.categoryid = c.id
 Group By c.id

Thanks!

Upvotes: 2

Views: 2710

Answers (2)

Anda Iancu
Anda Iancu

Reputation: 530

Check http://sqlfiddle.com/#!2/9a132/1

SELECT *
FROM Categories c 
LEFT JOIN -- Items i 
(
    SELECT * FROM Items WHERE LOCATE(id,
    (
        SELECT GROUP_CONCAT(it.itemids) AS its
        FROM (
                SELECT (SUBSTRING_INDEX(GROUP_CONCAT(CONVERT(id, CHAR(8))
                                                     ORDER BY id DESC), ',', 2)) AS itemids
                FROM Items
                GROUP BY categoryid
            ) it
    )) <> 0
) i
ON i.categoryid = c.id;

where N=2 is: SUBSTRING_INDEX(GROUP_CONCAT(CONVERT(id, CHAR(8)) ORDER BY id DESC), ',', 2)

Because GROUP_CONCAT is by default sort ASC ; above will become SUBSTRING_INDEX(GROUP_CONCAT(CONVERT(id, CHAR(8)) ), ',', -2). But GROUP_CONCAT result is truncated to max len 1024 (group_concat_max_len).

Upvotes: 2

Bernhard Barker
Bernhard Barker

Reputation: 55649

I'm not saying it's efficient, but it should work:

SELECT c.*, i.id, i.itemname
FROM categories c
LEFT JOIN
  (SELECT i.*
   FROM items i
   LEFT JOIN items i2 ON i.categoryid = i2.categoryid AND i2.id > i.id
   GROUP BY i.id
   HAVING COUNT(*) < 2         # this 2 = N
  ) i ON c.id = i.categoryid

Upvotes: 4

Related Questions