SELECT items that has one or more specific TAGS

Question

I have three database tables.

One with items, one with tags and one with items_has_tags.

Tags

CREATE TABLE `tags` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `tags` (`id`, `name`) VALUES
(1, 'tag one'),
(2, 'tag two'),
(3, 'tag three'),
(4, 'tag four'),
(5, 'tag five');

Items

CREATE TABLE `items` (
  `id` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(30) NOT NULL
) COMMENT='';
INSERT INTO `items` (`id`, `name`) VALUES
(1, 'first item'),
(2, 'second item'),
(3, 'third item'),
(4, 'fourth item');

Item has tag

CREATE TABLE `item_has_tag` (
  `item_id` int NOT NULL,
  `tag_id` int NOT NULL,
) COMMENT='';

INSERT INTO `item_has_tag` (`item_id`, `tag_id`) VALUES
(1, 2),
(1, 3),
(1, 4),
(2, 1),
(2, 3),
(2, 4),
(3, 2),
(3, 3),
(3, 4),
(1, 2),
(4, 3),
(4, 4),
(4, 5);

I want to only select items that have all the specific tags, such as the items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example.

edit

The amount of specific tags varies from 1 to 4-5.

edit It is the items list that needs to be returned.

Would you like to help me?

Answer 1

For a specific tags list you can simply calculate the amount of tags from this list attached to each item using

SELECT item_id, SUM(tag_id IN (specific_tags_list))
FROM item_has_tag
GROUP BY item_id

To obtain the list of items which have all of the tags in tags list you must compare calculated SUM() with the amount of tags in the list:

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING amount_of_tags_in_list = SUM(tag_id IN (specific_tags_list))

items that item_has_tag.tag_id = 1 AND item_has_tag.tag_id = 2 AND item_has_tag.tag_id = 3 for example

For this case the query will be

SELECT item_id
FROM item_has_tag
GROUP BY item_id
HAVING 3 = SUM(tag_id IN (1,2,3))

PS. The fields pair (item_id, tag_id) must be defined as unique by index (in any order, maybe primary) in the table item_has_tag structure.

---

UPDATE

---

Your code works very well but I get into trouble when I try to get the result from the items list SELECT items.* FROM items, item_has_tag GROUP BY item_has_tag.item_id HAVING 3 = SUM(item_has_tag.tag_id IN (1,2,3)) Error "SELECT list is not in GROUP BY clause"

Variant 1:

SELECT items.*
FROM items, ( SELECT item_id
              FROM item_has_tag
              GROUP BY item_id
              HAVING 3 = SUM(tag_id IN (1,2,3))
            ) subquery
WHERE items.id = subquery.item_id

Variant 2:

SELECT items.name /* , another fields */
FROM items, item_has_tag
WHERE items.id = item_has_tag.item_id
GROUP BY items.name /* , another fields */
HAVING 3 = SUM(item_has_tag.item_id IN (1,2,3))

Variant 3: disable session ONLY_FULL_GROUP_BY SQL Mode (not recommended).

PS. Variants 1 and 2 can be freely converted to JOIN form.

Answer 2

If i understood you right, you're simply looking for the OR operator

SELECT item_id
FROM item_has_tag
WHERE tag_id = 1 OR tag_id = 2 OR ...

Answer 3

For an explicit labels show you can just figure the measure of labels from this rundown appended to every thing utilizing

SELECT item_id, SUM(tag_id IN (specific_tags_list)) 
FROM item_has_tag 
Gathering BY item_id 

To get the rundown of things which have the majority of the labels in labels show you should think about determined Aggregate() with the measure of labels in the rundown:

SELECT item_id 
FROM item_has_tag 
Gathering BY item_id 
HAVING amount_of_tags_in_list = SUM(tag_id IN (specific_tags_list))