Get the last element for many-to-many relationships in MySQL

Question

I have 3 tables: categories , items and items_categories . This is a many-to-many relationship. The structure of these tables is as follows:

 CREATE TABLE `categories` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) CREATE TABLE `items_categories` ( `item_id` int(10) unsigned NOT NULL, `category_id` int(10) unsigned NOT NULL ) CREATE TABLE `items` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `thumb` varchar(255) COLLATE utf8_unicode_ci NOT NULL ) 

I have indexes, but I retired because I do not need it for this example.

I need a query that returns me all the categories associated with the last element for each of them and that it is NOT repeated. Since elements can have many categories, let's say that element No. 300 (last) has categories # 1 and # 2 in the final category of results # 1 and # 2, different elements will be combined.

Now I have this request, but it does not do what I definitely want:

 SELECT category_id, c.name, c.slug, item_id, i.name, i.thumb FROM items_categories AS ic INNER JOIN categories AS c ON c.id = ic.category_id INNER JOIN (SELECT * FROM items ORDER BY id DESC) AS i ON i.id = ic.item_id GROUP BY c.id ORDER BY c.id ASC 

It returns me duplicate results and returns the first element, not the last.

Dummy data: http://pastebin.com/D75tr4Ry

How can i do this? Thanks!

Answer 1

You just forgot to add limit to the subquery. After using the limit, you can get all categories for the last item.

Try the following:

 SELECT category_id, c.name, c.slug, item_id, i.name, i.thumb FROM items_categories AS ic INNER JOIN categories AS c ON ic.category_id = c.id INNER JOIN (SELECT * FROM items ORDER BY id DESC LIMIT 1) AS i ON ic.item_id = i.id GROUP BY c.id ORDER BY c.id ASC