MySQL Limit with Many to Many Relationship

Question

Given a SCHEMA for implementing tags

ITEM ItemId, ItemContent

TAG TagId, TagName

ITEM_TAG ItemId, TagId

What is the best way to limit the number of ITEMS to return when selecting with tags?

SELECT i.ItemContent, t.TagName FROM item i 
INNER JOIN ItemTag it ON i.id = it.ItemId 
INNER JOIN tag t ON t.id = it.TagId

is of course the easiest way to get them all back, but using a limit clause breaks down, because you get an duplicate of all the items for each tag, which counts toward the number of rows in LIMIT.

Answer 1

Maybe something like

select i.ItemContent, t.TagName from (SELECT ItemId, ItemContent FROM item limit 10) i
INNER JOIN ItemTag it ON i.ItemId = it.ItemId --You will miss tagless items here!
INNER JOIN tag t ON t.id = it.TagId

Answer 2

My first suggestion is to use a subquery to generate the list of item ID's and return items matching those item ID's. But this doesn't include the TagName in your result set. I'll submit a separate answer with another solution.

SELECT i.ItemContent
FROM item AS i
WHERE i.id IN (
  SELECT it.ItemId
  FROM ItemTag AS it
    INNER JOIN tag AS t ON (t.id = it.TagId)
  WHERE t.TagName IN ('mysql', 'database', 'tags', 'tagging')
);

This is a non-correlated subquery, so a good SQL engine should factor it out and run it only once.

Answer 3

My second solution uses a MySQL function GROUP_CONCAT() to combine all tags matching the item into a comma-separated string in the result set.

SELECT i.ItemContent, GROUP_CONCAT(t.TagName ORDER BY t.TagName) AS TagList
FROM item AS i 
  INNER JOIN ItemTag AS it ON i.id = it.ItemId 
  INNER JOIN tag AS t ON t.id = it.TagId
GROUP BY i.ItemId;

The GROUP_CONCAT() function is a MySQL feature, it's not part of standard SQL.

Answer 4

You could also use Distinct/Group By:

SELECT DISTINCT TagID, TagName FROM ((TAG T INNER JOIN ITEM_TAG I_T ON T.TagID = I_T.TagID) INNER JOIN ITEM I ON I_T.ItemID = I.ItemID) GROUP BY TagID, TagName