SELECT items that has one or more specific TAGS 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?
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.