CODEWITHSUNDEEP
mysqljoin
Shivan
Shivan

Reputation: 137

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

Upvotes: 1

Views: 2680

Answers (2)

Jester
Jester

Reputation: 1408

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.

Upvotes: 2

J.K
J.K

Reputation: 1374

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

Upvotes: 2

Related Questions