2

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?

  • How many tags do you have?more than 5? – Mihai Jan 21 '14 at 19:23
  • About 200 and number will rise. And number of items goes to thousands. – meridius Jan 21 '14 at 19:24
  • 1
    If you address those repetitive "tag " words in each tag name, than those are just example tag names. The real ones are more various of course. ;) And still, those are just names. I reference tags by their ID. – meridius Jan 21 '14 at 19:34
  • 1
    The tags.name column is fine. What is wrong is the comma separated list. This should be replaced with an intermediate table. then you will be properly referencing the tags(id). Now you can have silly values in the items(tags) column, values like '1,2,3,1,1,2,2,5789' where a tag is "referenced" twice or a tag that doesn't exist is referenced. – ypercubeᵀᴹ Jan 21 '14 at 19:36
  • 2
    and a similar question at SO: Is storing a delimited list in a database column really that bad? about why this is a really bad idea. – ypercubeᵀᴹ Jan 21 '14 at 19:47
3

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)

Fiddle

  • Thank you. I knew FIND_IN_SET and JOIN are the way to go, I just don't know why I didn't put those two together. :) – meridius Jan 21 '14 at 19:57

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.