sqlmysqlinner-joinaggregate-functionsgroup-concat

How to join 3 tables with group_concat

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.

Solution

  • 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: