ivannaxara ivannaxara - 1 year ago 109
MySQL Question

SQL Sentence Prestashop

I´m trying to get a sql sentence of the table ps_orders, of prestashop, that returns me the following data: "I wanna get the abandoned orders, with a value greater than 20, just for registered users without a more recent order".
I have tried the following sentence:

SELECT * , MAX( "date_add" ) AS "Date" FROM "ps_orders" WHERE "id_customer" >0 AND "shipping_number" >0 AND "total_paid" >20 GROUP BY "id_customer"

So I have "abandoned orders" using
I also have "registered users" using
and higher values than 20 with
my problem is on selecting just if they havent got any later bought. Any help will be preciated.

Answer Source

I don't know Prestashop so much, then I only work with your SQL problem. Sorry for the mistakes.

You can do it with a subselect, I think in something like:

SELECT pso.*, pso.date_add AS 'Date'
FROM  ps_orders pso
WHERE pso.id_customer > 0 AND pso.shipping_number > 0 AND pso.total_paid > 20 
AND pso.date_add = (SELECT MAX(pso2.date_add) FROM ps_orders pso2 WHERE pso2.id_customer = pso.id_customer)
GROUP BY pso.id_customer

With this you force the registers with no other date_add in the future.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download