[Solucionado] Consulta de ventas.

[djmortis]

¿Que tengo mal en esta consulta?

 

SELECT CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Cliente, n.`product_name` AS Producto, n.`product_quantity` AS Cantidad, 'reference' AS Referencia, 'payment' AS Forma de pago, 'valid' AS Pagado FROM  `ps_orders` o LEFT JOIN  `ps_customer` g ON g.`id_customer` = o.`id_customer` LEFT JOIN  `ps_order_detail` n ON n.`id_order` = o.`id_order` LEFT JOIN  `ps_orders` n ON 'id_order' = n.`id_order` WHERE o.date_upd BETWEEN  '2014-01-01' AND  '2014-12-31' AND n.`product_reference` =  "PB001" LIMIT 0 , 30

 

Necesito que me de un CSV con...

 

Cliente : ps_customers.firstname y ps_customers.lastname

Producto: ps_order_detal.product_name

Cantidad: ps_order_detail.product_quantity

Referencia: ps_orders.reference

Forma de pago: ps_orders.payment

Pagado: ps_orders.valid

Edited May 28, 2014 by djmortis (see edit history)

[ventura]

prueba con algo asi

SELECT d.id_order AS Pedido, d.reference AS Referencia, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Cliente, o.total_paid_real AS Cantidad, o.payment AS Forma_pago, o.valid AS Pagado, o.date_upd AS Fecha
FROM ps_orders 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_order_history h ON ( h.id_order = o.id_order ) 
WHERE o.date_upd
BETWEEN  '2014-01-01'
AND  '2014-12-31'

GROUP BY d.id_order
LIMIT 0 , 30

Edited May 28, 2014 by ventura (see edit history)

[djmortis]

No exactamente pero me has dado la solución. Gracias Ventura.

 

Era así como yo quería...

SELECT d.id_order AS Pedido, CONCAT_WS(  ' ', g.firstname, g.lastname ) AS Cliente, n.`product_name` AS Producto, n.`product_quantity` AS Cantidad, d.reference AS Referencia, o.payment AS Forma_pago, o.valid AS Pagado
FROM ps_orders 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_order_detail` n ON n.`id_order` = o.`id_order` 
LEFT JOIN ps_order_history h ON ( h.id_order = o.id_order ) 
WHERE o.date_upd
BETWEEN  '2014-01-01'
AND  '2014-12-31'

GROUP BY d.id_order
LIMIT 0 , 30