问题描述:

I don't not how to say what i need to do in programing lang. This is my request

SELECT pl.id_product, CONCAT( pl.name, ' ', al.name ) AS produkt, al.id_attribute, com.id_product_attribute

FROM ps_stock_available stock

LEFT JOIN ps_product_lang pl ON pl.id_product = stock.id_product

LEFT JOIN ps_product_attribute_combination com ON stock.id_product_attribute = com.id_product_attribute

LEFT JOIN ps_attribute_lang al ON al.id_attribute = com.id_attribute

LEFT JOIN ps_product_attribute pa ON ( stock.id_product_attribute = pa.id_product_attribute )

WHERE pl.id_lang =1

AND al.id_lang =1

ORDER BY pl.id_product

And this is result

This is possible to print

Faded Short Sleeve T-shirts S Orange

Faded Short Sleeve T-shirts S Blue

网友答案:

It looks like you could use a a GROUP_CONCAT function on al.name, and then concatenate the result from that with pl.name.

Something like this:

 CONCAT(pl.name,' ',
   GROUP_CONCAT(al.name  ORDER BY al.id_attribute SEPARATOR ' ')
 ) AS produkt

To do that, you'd also need to add a GROUP BY clause to the query (immediately preceding or in place of, the ORDER BY), for example:

 GROUP BY pl.id_product, com.id_product_attribute

That will effectively "collapse" the rows, so the query would return:

id_produkt  produkt                               id_product_attribute 
----------  ------------------------------------- --------------------
         1  Faded Short Sleeve T-shirts S Orange                     1
         1  Faded Short Sleeve T-shirts S Blue                       2

(That's assuming that pl.name is returning the "Faded Short Sleeve T-shirts" portion, and the al.name is returning (e.g.) the "S" and "Orange" portions.)

With those changes to the query, if you also return al.id_attribute in the SELECT list, then MySQL will return the value of that column from only a single row in the group. To get all of the values returned as a comma separated list, you could again use the GROUP_CONCAT function in the SELECT list, e.g.

GROUP_CONCAT(al.id_attribute ORDER BY al.id_attribute) AS id_attribute_list
相关阅读:
Top