I am using Cubecart for a customer website, and frustratingly they have used comma separated values to associate certain option IDs to products. I now need to create a custom table which gets all this information out into a useful format so that I can assign different product codes for varying combinations of product options.
I have simplified the content to make it easier to understand.
**Products Table**
Product ID Product Code Assign Key
1 ABC 23,45
1 HIJ 23
1 KLM 45
2 DEF 10,28
2 GHI 10
2 NOP 28
**Assign Table**
Product ID Assign ID Value ID
1 23 1
1 45 2
2 10 3
2 28 4
**Values Table**
Value ID Value
1 Red
2 Large
3 Blue
4 Small
I can work out how I would connect the products table directly to the values table, if the assign Keys were actually value IDs, but I can’t work out how to do it with the assign table in the middle. I need to connect products to assign and assign to values.
SELECT
t1.product_code,
t1.product_id,
t1.assign_key,
GROUP_CONCAT(t2.value_name)
FROM products t1
LEFT JOIN values t2 ON FIND_IN_SET(t2.value_id, t1.assign_key)
GROUP BY t1.assign_key
Expected output would be:
Product ID Product Code Values
1 ABC Red, Large
1 HIJ Red
1 KLM Large
2 DEF Blue, Small
2 GHI Blue
2 NOP Small
I tried adding another join in the middle, but cannot work out how to group concat twice.
HELP!