Question 177595 in PHP Question posted in
Official manual can be found here.

Php – How to join 3 tables with group_concat

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

HELP!

Tags:

2

Answers


  1. 0

    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".

    Login or Signup to reply.
  2. 0

    You could use find_in_set… 23 and 45 will be found in 23,45 at position 1 and 2 respectively. This would be the basis for join and order by.

    select products.ProductID
     , products.ProductCode
     , products.AssignKey
     , group_concat(valuess.ValueID order by find_in_set(assign.AssignID, products.AssignKey) separator ',') as ValueIDs
     , group_concat(valuess.Value order by find_in_set(assign.AssignID, products.AssignKey) separator ',') as ValueNames
from products
join assign on products.ProductID = assign.ProductID and find_in_set(assign.AssignID, products.AssignKey) > 0
join valuess on assign.ValueID = valuess.ValueID
group by products.ProductID, products.ProductCode

    SQLFiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search