0

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?

  • Don't you really want to start with tags.name IN ('dog', cat', bird')? – Rick James Jan 9 at 16:07
  • Yhea, you have a point, but in this case, the situation is that we have the id's already, the SQL code above is just dummy examples – PaRoxUs Jan 11 at 12:47
0

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.

  • Thank you, I think I can work with that, but I need items and not the item_id – PaRoxUs Jan 9 at 10:42
  • @PaRoxUs Add items table into FROM section and obtain the field which you need. – Akina Jan 9 at 10:43
  • Could this approach with a join statement too? – PaRoxUs Jan 9 at 10:44
  • @PaRoxUs You may add it with JOIN or using cartesian - it doesn't matter. – Akina Jan 9 at 10:47
  • 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" – PaRoxUs Jan 9 at 11:06
0

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 ...
0

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))

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.