BOTJr. BOTJr. - 1 month ago 6
MySQL Question

relation bw group_concated columns in mysql

I know my question is kind of weird but i will try to make it as clear as possible.

products_table
ID | code | product_variants_id | product_name | variants | variants_value
1 | 1| 123451 | beer cake | color | blue
2 | 1| 123451 | beer cake | temperature | cold
3 | 1| 123451 | beer cake | weight | 0.5
4 | 2| 123453 | ad wrap | color | green
5 | 2| 123453 | ad wrap | weight | 1


This is what my final product table looks like , now to display the products , this is what i did.

SELECT xx.code, GROUP_CONCAT(xx.variants) AS variants,
GROUP_CONCAT(xx.variants_value) AS variants_value ,
xx.product_name, xx.product_variants_id
FROM products_table xx
GROUP BY xx.product_variants_id


After this query my table looks something like this

code | product_variants_id | product_name | variants | variants_value
1 | 123451 | beer cake | color,temperature,weight| blue,cold,0.5
2 | 123453 | ad wrap | color,weight | green,1


Now i can simply run through this table and get products one by one and display it's variants.

My real question, is there any way to link these concatenated results that i just obtained?

For example:
at the server side i can get the variant
color
, how can i get the corresponding value of the
color
?

What would be my approach ?

Answer

Concatenate the variant and corresponding values together first, then do the group_concat():

SELECT xx.code, GROUP_CONCAT(concat(xx.variants,':',xx.variants_value)) AS variants_and_values, xx.product_name, xx.product_variants_id
FROM products_table xx
GROUP BY xx.product_variants_id, xx.product_name, xx.code