How to join 3 tables with group_concat

Question

I am using Cubecart for a customer website, and they have used comma-separated values to associate certain option IDs to products. I need to create a custom table which gets all this information out into a format so that I can assign different product codes for varying combinations of product options.

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:

Product ID	Product Code	Assign IDs	Value IDs	Values
1	ABC	23,45	1,2	Red, Large
1	HIJ	23	1	Red
1	KLM	45	2	Large
2	DEF	10,28	3,4	Blue, Small
2	GHI	10	3	Blue
2	NOP	28	4	Small

I tried adding another join in the middle, but cannot work out how to group concat twice.

I cannot just separate the assign keys so that there is one value per row, because the whole point is that the product code is only relevant to the combination of BOTH assign keys.

Answer 1

This query produced the desired results:

SELECT 
  p.product_id,
  p.product_code,
  GROUP_CONCAT( SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1)
          ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1)) as AssignIDs,
  GROUP_CONCAT( a.value_id ORDER BY a.value_id) as ValueIDs,
  GROUP_CONCAT( v.value_name ORDER BY a.value_id) as "Values"
FROM products p
INNER JOIN assign a ON a.product_id = p.product_id
INNER JOIN value v ON v.value_id = a.value_id
WHERE FIND_IN_SET(a.assign_id,p.assign_key) > 0
GROUP BY p.product_id, p.product_code;

output:

product_id	product_code	AssignIDs	ValueIDs	Values
1	ABC	23,45	1,2	Red,Large
1	HIJ	23	1	Red
1	KLM	45	2	Large
2	DEF	10,28	3,4	Blue,Small
2	GHI	10	3	Blue
2	NOP	28	4	Small

A (short) plan in steps is done in the DBFIDDLE, but it is basically "keep adding stuff to this query, until we have all desired values", and then apply GROUP_CONCAT, and a proper GROUP BY

(An explanation about the error "ONLY_FULL_GROUP_BY", and how to solve it in your query, is in the DBFIDDLE too.)

Some notes:

• SUBSTRING_INDEX(SUBSTRING_INDEX(assign_key,',',FIND_IN_SET(a.assign_id,p.assign_key)),',',-1) is done to find the nth element in a comma separated string.

  A short example: This will return d
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a,b,c,d,e',',',4),',',-1)

• The ordering within the "Values" is done on ValueID, to match the description with the ID, so we have "Red,Large" (and not an alphabetic ordering of these values) because we have "1,2" and Red has the value "1".