Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
How many tags do you have?more than 5? –  Mihai Jan 21 at 19:23
    
About 200 and number will rise. And number of items goes to thousands. –  meridius Jan 21 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 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 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 at 19:47
show 6 more comments

1 Answer

up vote 2 down vote accepted

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

share|improve this answer
    
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 at 19:57
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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