CODEWITHSUNDEEP
mysqljoin
Scott C Wilson
Scott C Wilson

Reputation: 20046

Mysql JOIN for items and tags

Consider three tables: one of items, one of tags on those items, and the third which maps tag ids to tag names.

Items: 

ITEM ID   ITEM NAME 
-------------------
1         Item 1
2         Item 2
3         Item 3

Tags: 

ID    TAG ID   ITEM ID 
1      1       1 
2      2       1 
3      3       1
4      1       2
5      1       3

Tag Names: 

TAG ID  TAG NAME
1       TAG_A 
2       TAG B 
3       TAG C

So only item 1 has tags TAG_A, TAG_B and TAG_C.

How do you do a select that retrieves all items which have TAG_A, TAG_B and TAG_C without doing 3 INNER JOINs? In other words, I know I can do a select and say 

INNER JOIN item_tags pt4 ON pt4.item_id = p.item_id  AND pt4.tag_id = 1 
INNER JOIN item_tags pt13 ON pt13.item_id = p.item_id  AND pt13.tag_id = 2
INNER JOIN item_tags pt19 ON pt19.item_id = p.item_id  AND pt19.tag_id = 3

but that's got to be inefficient, right?

What about a subquery, like

SELECT * FROM items WHERE ... AND item_id IN (SELECT item_id FROM item_tags WHERE tag_id IN ( 1, 2, 3 ))

(This exact query wouldn't work - it's an OR on tags, but this is what I'm going for. )

Upvotes: 0

Views: 277

Answers (2)

Devart
Devart

Reputation: 122032

Try this query -

SELECT t.item_id FROM tags t
  JOIN tag_names tn
    ON t.tag_id = tn.tag_id
GROUP BY
  t.item_id
HAVING
  COUNT(DISTINCT t.tag_id) = (SELECT COUNT(*) FROM tag_names)

Upvotes: 2

DRapp
DRapp

Reputation: 48179

The first query (aliased sqlvars) creates a single @NumTags variable of how many tags are in the tag name table so it doesn't need to do it on every qualifying tags group by.

The second query (aliased HasAllTags) gets items, grouped by itemid having the total count equal to that of the @NumTags.

Finally, the HasAllTags is joined to items to pull the data.

select
      I.*
   from
      ( select @NumTags := (select count(*) from TagName )) sqlvars,
      ( select t.ItemID
           from Tags t
           group by t.ItemID
           having count(*) = @NumTags ) HasAllTags
         JOIN Items I
            on HasAllTags.ItemID = I.ItemID

Upvotes: 0

Related Questions