CODEWITHSUNDEEP
mysqlsqlcsv
Sander
Sander

Reputation: 1

mySQL: How to find an item with all specific (included) tags, but cannot include other specific (excluded) tags?

I have a complicated dataset that contains a variety of data, that needs various filters. This database consists of items that can have various tags.

  1. A single item can have multiple tags (30+).
  2. The input consists of two comma separated strings:
  1. Find all the itemID's that have all tags from the included string but can not have a tag that is in the excluded string.
  2. It is possible that an item has multiple tags with the same value. This is because tags have time attached to it.

Example:

Included = 'Violet'
Excluded = 'Crimson'

Will return all items having a 'Violet' tag but if an item has 'Crimson' tag too it will not be returned.

Currently I am working with 3 tables to create filter for this problem

Main table:

ItemID Other columns...
ID_0 ...
ID_1 ...
ID_2 ...
ID_3 ...
... ...

Linking table:

ItemID TagID
ID_0 TAG_2
ID_1 TAG_1
ID_1 TAG_2
ID_2 TAG_2

Tag table:

TagID TagName
TAG_0 Teal
TAG_1 Violet
TAG_2 Crimson
TAG_3 Khaki
TAG_4 Fuscia

I have tried the following:


SET @returnedValuesLength = IF(CHAR_LENGTH(@returnedValues) > 0, CHAR_LENGTH(@returnedValues) - CHAR_LENGTH(REPLACE(@returnedValues, ',', '')) + 1, 0);


@myIncludedValues = 'Crimson, Violet'
@myExcludedValues = 'Khaki'



SELECT DISTINCT ItemID 
FROM   main_table 
       JOIN linking_table Lt 
         ON main_table.ItemID = Lt.ItemID
       JOIN tag_table Tt 
         ON Lt.TagID = Tt.TagID
WHERE  Find_in_set(TagName, @myIncludedValues) 
       AND NOT Find_in_set(TagName, @myExcludedValues) 
GROUP  BY ItemID 
HAVING Count(DISTINCT TagName) = @returnedValuesLength 
ORDER  BY ItemID;

Upvotes: 0

Views: 36

Answers (1)

Akina
Akina

Reputation: 42632

SELECT {columns}
FROM {tables}
GROUP BY {columns}
HAVING SUM(FIND_IN_SET(tag, @myIncludedValues))  -- at least one tag from the list
   AND !SUM(FIND_IN_SET(tag, @myExcludedValues)) -- none tags from the list

Upvotes: 1

Related Questions