Geek Answers Handbook

Select an item matching multiple tags

It seems very simple, but I can not understand.

I have a table called "item_tags", and I want to select all the elements corresponding to tags 1 and 2 (as in each element, they must have both tags).

How would this be done in mysql?

Create table:

CREATE TABLE `item_tags` ( `uid_local` int(11) NOT NULL DEFAULT '0', `uid_foreign` int(11) NOT NULL DEFAULT '0', `sorting` int(11) NOT NULL DEFAULT '0', KEY `uid_local` (`uid_local`), KEY `uid_foreign` (`uid_foreign`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Thanks!

+6
sql mysql many-to-many
source share
2 answers

Using:

  SELECT i.uid FROM ITEMS i JOIN ITEM_TAGS it ON it.uid_local = i.uid AND it.uid_foreign IN (1, 2) GROUP BY i.uid HAVING COUNT(DISTINCT it.uid_foreign) = 2

You must have a GROUP BY and HAVING clause, and the number of label identifiers must equal the number of tags that you specify in the IN clause.

+11
source share

something like that?

 SELECT i.* from items i inner join items_tags it on i.id = it.item_id inner join tags t on t.id = it.tag_id WHERE t.name in ('tag1', 'tag2');

EDIT:

suppouse you have items_tags: (item_id, tag_id) as a table

0
source share

More articles:


All Articles