List Combinations query

[Koen Amant]

Dear, 

 

I made a query to list the combinations of an article. 

 

SELECT        ps_product_attribute.id_product, ps_attribute_lang.id_attribute, CONCAT(ps_attribute_group_lang.public_name, ': ', ps_attribute_lang.name) AS Attr, 

                         ps_product_attribute_combination.id_product_attribute, ps_attribute_group_lang.id_attribute_group

FROM            ps_product_attribute INNER JOIN

                         ps_product_attribute_combination ON ps_product_attribute.id_product_attribute = ps_product_attribute_combination.id_product_attribute INNER JOIN

                         ps_attribute ON ps_product_attribute_combination.id_attribute = ps_attribute.id_attribute INNER JOIN

                         ps_attribute_group_lang ON ps_attribute.id_attribute_group = ps_attribute_group_lang.id_attribute_group INNER JOIN

                         ps_attribute_lang ON ps_attribute.id_attribute = ps_attribute_lang.id_attribute AND ps_attribute_group_lang.id_lang = ps_attribute_lang.id_lang

WHERE        (ps_attribute_lang.id_lang = 1) AND (ps_product_attribute.id_product = 195)

 

This gives the following result

 

id_product id_attribute Attr id_product_attribute id_product_attribute_group

195 16 Color: WHITE 5 1 195 18 Size: M 5 3

195 16 Color: WHITE 6 1 195 19 Size: L 6 3

 

What I want is this result...

id_product   Color Size id_product_attribute

195   WHITE M 5

195   WHITE L 5          

 

I know how to put them all in one field with the GROUP_CONCAT command but not like the view above... :-(

 

Greetings,

 

Koen

Edited March 1, 2015 by Koen Amant (see edit history)