SQL Query Problem

[ukbaz]

Hi I am trying to get a list of orders with products ordered and customer addresses and chosen carrier.

I have managed to get this to work, but where customers have bought more than one product I am getting rows for each product bought like this!

62118 Preparation in progress 830-080

62118 Preparation in progress 816-052

62118 Preparation in progress 816-050

How do I merge so that products appear in one record and not separately? Can anyone help me here?

SQL Query is:

SELECT
    d.id_order,
    os.name AS Status,
    d.product_reference,
    d.product_price,
    d.product_quantity,
    d.product_weight,
    o.date_upd AS Date,
    CONCAT_WS(' ', g.firstname, g.lastname) AS Customer,
    ad.company,
    ad.address1,
    ad.address2,
    ad.postcode,
    ad.city,
    ste.name AS state,
    cl.name AS country,
    ad.other AS message,
    ad.phone,
    ad.phone_mobile,
    carrier.name AS carrier,
    gl.name AS group_name
FROM
    ps_order_detail d
LEFT JOIN
    ps_orders o
ON
    (d.id_order = o.id_order)
LEFT JOIN
    ps_customer g
ON
    (o.id_customer = g.id_customer)
LEFT JOIN
    ps_address ad
ON
    (
        o.id_address_delivery = ad.id_address
    )
LEFT JOIN
    ps_state ste
ON
    (ste.id_state = ad.id_state)
LEFT JOIN
    ps_country_lang cl
ON
    (cl.id_country = ad.id_country)
LEFT JOIN
    ps_carrier carrier
ON
    (
        carrier.id_carrier = o.id_carrier
    )
LEFT JOIN
    ps_group_lang gl
ON
    (
        g.id_default_group = gl.id_group
    ) AND gl.name LIKE 'customer%'
LEFT JOIN
    ps_order_state_lang os
ON
    (
        o.current_state = os.id_order_state
    )
WHERE o.current_state in (2,3)
AND (o.`date_add` > DATE_SUB(now(), INTERVAL 1 DAY));
ORDER BY
    o.id_order
DESC
 

[EvaF]

 you should use "GROUP BY" and specify the languaage
I do not know what you want to achieve, but f.e. the sql can look like this:

SELECT
    d.id_order,
    os.name AS Status,
    GROUP_CONCAT( d.product_reference,'|',  d.product_price,'|',  d.product_quantity) as products,
    SUM(d.product_quantity*d.product_price) as products_price,
    SUM(d.product_quantity) as products_qty,
    SUM(d.product_weight) as products_weight,
    o.date_upd AS Date,
    CONCAT_WS(' ', g.firstname, g.lastname) AS Customer,
    ad.company,
    ad.address1,
    ad.address2,
    ad.postcode,
    ad.city,
    ste.name AS state,
    cl.name AS country,
    ad.other AS message,
    ad.phone,
    ad.phone_mobile,
    carrier.name AS carrier,
    gl.name AS group_name
FROM
    ps_order_detail d
LEFT JOIN
    ps_orders o ON (d.id_order = o.id_order)
LEFT JOIN
    ps_customer g ON (o.id_customer = g.id_customer)
LEFT JOIN
    ps_address ad ON   (     o.id_address_delivery = ad.id_address    )
LEFT JOIN
    ps_state ste ON  (ste.id_state = ad.id_state)
LEFT JOIN
    ps_country_lang cl ON (cl.id_country = ad.id_country) and cl.id_lang = 1
/* use specific lang */    
LEFT JOIN
    ps_carrier carrier ON    ( carrier.id_carrier = o.id_carrier    )
LEFT JOIN
    ps_group_lang gl ON    ( g.id_default_group = gl.id_group  ) AND gl.name LIKE 'customer%' and gl.id_lang=1
/* use specific lang */        
LEFT JOIN
    ps_order_state_lang os ON ( o.current_state = os.id_order_state and os.id_lang=1    )
/* use specific lang */        
WHERE  o.current_state in (2,3) AND (o.`date_add` > DATE_SUB(now(), INTERVAL 1 DAY))
GROUP BY o.id_order
ORDER BY o.id_order DESC

 

[ukbaz]

Thank you SO much!

Worked perfectly!!