SQL query query

[GMM148]

Hi, I'm using a great SQL table someone created to bring in the data of particular customers for a particular product.

BUT, I would like to add the quantity of the product - normally its just one, but there are occasions they will order more.

The SQL as it stands is below - if anyone can assist I'd be very grateful

Thanks

SELECT DISTINCT c.`id_customer`, CONCAT(c.`firstname`,' ', c.`lastname`) as customer, c.`email` 
FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = 8

[musicmaster]

Add o.product_quantity to the fields

[GMM148]

Thanks MM, am getting message that o.product_quantity doesn't exist in tables. Tried changing o. to ps_ with same results.

Any thoughts appreciated.

Thanks

[GMM148]

What I'd like to achieve is a table like this - where id_state is the Order Status ('Delivered' etc). I've got the first three columns but 

id_customer	customer	email	qty	id_state
7	Ant	[email protected]	qty ordered	DELIVERED or any state (whichever is possible)

 

I've got the SQL working to achieve the first three columns, but can't crack the Qty and Order Status - I am presuming that I need to use 'product_quantity' from ps_order_detail and id_order_state from ps_order_state, but if anyone can insert it correctly into the SQL query I have already that would be really appreciated. Thanks

ps_order_detail - product_quantity

I want to show the number ordered by all customers for the product_id specificied

ps_order_state - id_order_state

 

SELECT DISTINCT c.`id_customer`, CONCAT(c.`firstname`,' ', c.`lastname`) as customer, c.`email` 
FROM `ps_customer` c LEFT JOIN `ps_orders` o ON (c.`id_customer` = o.`id_customer`) LEFT JOIN `ps_order_detail` od ON o.`id_order` = od.`id_order`
WHERE od.`product_id` = 8

 

ps_order_detail - product_quantity

I want to show the number ordered by all customers for the product_id specificied

ps_order_state - id_order_state

 

[musicmaster]

Sorry, it should be od.product_quantity to the fields

[mistik777]

With this code you get info you want and more

 

Set @product_id="8800";

SELECT g.`id_customer` AS id_customer, g.firstname AS Customer, g.email AS email, d.id_order, o.date_upd AS fecha, d.product_name AS producto, d.product_reference AS REF, z.upc AS UPC, d.product_quantity AS uds, d.unit_price_tax_incl AS PVP, -x.total_discount_tax_incl AS CUPONES, x.total_shipping_tax_incl AS PORTES, x.total_paid_tax_incl AS TOTAL, y.name AS ESTADO, w.payment_method AS PAGO
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_stock_available s ON (d.product_id = s.id_product)
LEFT JOIN ps_address ad ON (o.id_address_delivery = ad.id_address)
LEFT JOIN ps_address ai ON (o.id_address_invoice = ai.id_address)
LEFT JOIN ps_group_lang gl ON ( g.id_default_group = gl.id_group ) 
LEFT JOIN ps_order_state_lang os ON ( o.current_state = os.id_order_state ) 
LEFT JOIN ps_order_invoice x ON (d.id_order = x.id_order)
LEFT JOIN ps_order_state_lang y ON (o.current_state = y.id_order_state)
LEFT JOIN ps_product_attribute z ON (d.product_reference = z.reference)
LEFT JOIN ps_order_invoice_payment v ON (d.id_order = v.id_order)
LEFT JOIN ps_order_payment w ON (v.id_order_payment = w.id_order_payment)
WHERE d.product_id=@product_id
GROUP BY d.id_order, d.product_name
ORDER BY d.id_order ASC;