ivannaxara ivannaxara - 6 months ago 47
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.


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.