mysql join on csv field?

Question

I have three tables like below

CREATE TABLE IF NOT EXISTS `cat` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `color_options` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);
INSERT INTO `cat` (`id`, `color_options`) VALUES (1, '1,2,3,4');


CREATE TABLE IF NOT EXISTS `template` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `cat_id` int(15) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

INSERT INTO `template` (`id`, `cat_id`) VALUES (1, 1);


CREATE TABLE IF NOT EXISTS `color` (
  `id` int(15) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `code` varchar(6) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

INSERT INTO `color` (`id`, `name`, `code`) VALUES
(1, 'Black', '000000'),
(2, 'Red', 'FF0000'),
(3, 'Blue', '0000FF'),
(4, 'Green', '00FF00');

If I run

SELECT * 
FROM template
LEFT JOIN cat ON cat.id=template.id;

then I will get

id  cat_id  color_options

1   1   1,2,3,4

How do I get the color options in text(Black,Red,BLue,Green) instead of plain numbers?

I tried using join but it doesn't work on csv field.

Thanks in advance

Normalize your database and you won't have problems like this. If you don't know what that means, I've heard good things about the book, Database Design for Mere Mortals. — Dan Bracuk, Jan 09 '14 at 01:34

score 5 · Answer 1 · edited May 23 '17 at 12:03

5

MySQL has a function FIND_IN_SET() that works with a string of comma-separated values. It returns the position of a matching element, or 0 if no match is found. See the documentation for details.

For example:

SELECT * 
FROM template
JOIN cat ON cat.id=template.id
JOIN color ON FIND_IN_SET(color.id, cat.color_options)

Note that using comma-separated lists spoils any opportunity to use indexes to improve query efficiency. It also has a lot of other disadvantages. See Is storing a delimited list in a database column really that bad?

edited May 23 '17 at 12:03

Community

1
1

answered Jan 09 '14 at 01:24

Bill Karwin

538,548
86
673
828

This will return 1 row for each colour, I think OP wants all the colour names in a single column – EkriirkE Jan 09 '14 at 01:28
This exactly a perfect answer for what I was trying to do. I'm amazed at how many StackOverflow answers were dozens of lines long and created temporary tables or defined functions when all along FIND_IN_SET existed. – PeterT May 17 '18 at 02:36

EkriirkE · Accepted Answer · 2014-01-09T01:32:39.383

A sub query with GROUP_CONCAT()

SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE CONCAT(',',cat.color_options,',') LIKE CONCAT('%,',color.id,',%')) color_options_text
FROM template
LEFT JOIN cat ON cat.id=template.id

Someone else used a function new to me, this also works & is cleaner to read

SELECT *,(SELECT GROUP_CONCAT(name) FROM color WHERE FIND_IN_SET(color.id,cat.color_options)) color_options_text
FROM template
LEFT JOIN cat ON cat.id=template.id

mysql join on csv field?

2 Answers2

Linked