MySQL join tables with multiple values in column

Question

I have two tables:

products:

+-----------+-------------------------+
|product_id |          colors         |
+-----------+-------------------------+
|     1     |      1001,1002,1004     |
+-----------+-------------------------+
|     2     |      1005,1002,1001     |
+-----------+-------------------------+

colors:

+--------------------+
|  id  |  color_rgb  |
+--------------------+
| 1001 | (24,251,89) |
+--------------------+
| 1002 |  (116,18,1) |
+--------------------+
| 1003 | (221,251,23)|
+--------------------+
| 1004 | (124,251,42)|
+--------------------+

All I want to do is to joing both tables like this:

SELECT * 
FROM products
JOIN colors ON (products.colors = colors.id)

But the problem is, it's going to display only something like that(for product_id = 1 let's say)

+-----------+-------------------------+--------------------+
|product_id |          colors         |  id  |  color_rgb  |
+-----------+-------------------------+---------------------
|     1     |      1001,1002,1004     | 1001 | (24,251,89) |
+-----------+-------------------------+--------------------+

Which takes only first(1001) value from colors. Is it possible to 'loop' throu colors and display every each of them?(or group by them?[GROUP BY does not work here])

Why in the name of all that's good in the world are you storing colors as csv data in the product table? You need something like a color_to_product link table if you're dealing with a many-to-many relationship : en.wikipedia.org/wiki/Database_normalization — CD001
– CD001, Commented Apr 20, 2016 at 9:49
I know, but it's already built DB, that I have to use and make some queries. But I have not idea how to join those two things really. — Shivan
– Shivan, Commented Apr 20, 2016 at 9:52
It is possible (sort of) but the query will end up being so convoluted, will perform so badly (especially on large data sets), that it would be better to simply pull the colour data into an array in a script. As the script loops through the product data it parses the colours and assigns the RGB values from the colours array. — CD001
– CD001, Commented Apr 20, 2016 at 10:02

Jester · Accepted Answer · 2016-04-20 10:44:45Z

2

This should do the job if i haven't made a mistake

If you want to match with any color in the string you can use LIKE

SELECT * 
FROM products
JOIN colors ON (product.colors LIKE CONCAT('%,', colors.id, ',%') OR product.colors LIKE CONCAT(colors.id, ',%') OR product.colors LIKE CONCAT('%,', colors.id) OR product.colors = colors.id)

I think this should work but not able to test it myself at the moment.

edited Apr 20, 2016 at 10:44

answered Apr 20, 2016 at 9:55

Jester

1,4081 gold badge9 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Shivan Over a year ago

Unofrtunately, this one is still showing me only first one..but thanks.

Shivan Over a year ago

Works flawlessly, you're amazing Mr. Jester!! By the way, you're checking possible concats so one of them will 'work'? or how does this one works?

Jester Over a year ago

No problem! i actually forgot about one case so i added it in my answer There are 4 cases either the id is at the start of the string followed by a comma and then wildcard, it's in the middle of the string so it has a comma in front and behind itself with wildcard, it can be at the end and only have a , in front of it and nothing behind it. or if there is only one color we would match it normally. Hope this makes sense xD

Shivan Over a year ago

Yes, it makes perfect sense -> it gets every each of them from left to right, including the very first one. Thank you very much ! (=

J.K · Accepted Answer · 2016-04-20 10:13:07Z

2

MySQL CONCAT function is used to concatenate two strings: make the p.colors in a format of ,1001,1002,1003, and compare p.colors with c.id

Eg: ,1001,1002,1003, (products) with %,1001,% (colors)

SELECT p.id as product_id, p.colors, c.id, c.color_rgb
FROM products p
INNER JOIN colors c ON (CONCAT(',', p.colors, ',') LIKE CONCAT('%,', c.id, ',%'))
ORDER BY p.id, c.id ASC

answered Apr 20, 2016 at 10:13

J.K

1,3741 gold badge12 silver badges27 bronze badges

2 Comments

Shivan Over a year ago

Thanks for explaination !:)

J.K Over a year ago

:-) It would be more convenient if the value is in this format -1001-1002-1003- in database

Collectives™ on Stack Overflow

MySQL join tables with multiple values in column

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related