MySQL – How to Select Items with One or More Specified Tags

MySQL

I have two tables.
One table holds 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');

Other table holds items, where each item has zero or more tags (their IDs) separated by comma:

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

How do I select only those items which have at least one of the wanted, say (2,3) tags?

Best Answer

Just use FIND_IN_SET function

SELECT * FROM items 
JOIN tags 
ON FIND_IN_SET(tags.id,items.tags)
WHERE tags.id IN(2,3)

Related Question