Shivan Shivan - 7 months ago 8
SQL Question

MySQL join tables with multiple values in column

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])

Answer

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))

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

Comments