SQL SELECT QUERIES

[liveman]

Looking for a query to pull info about customers that have been purchased a product + payment has been verified via Paypal any help appreciated + would be great of this could one day be built-in

[noesac]

Here you go. I don't know what information you want to pull back (e.g. email, address, products purchased etc), let me know and I can update the query for you. So far it's very simple. I'm not very good at SQL so some members here might have a more elegant solution.


SELECT ps_customer.firstname, ps_customer.lastname

FROM ps_customer LEFT JOIN ps_orders ON ps_customer.id_customer=ps_orders.id_customer

WHERE payment='PayPal' and valid=1


Or if you want to search based on status = "Order shipped":

SELECT ps_customer.firstname, ps_customer.lastname

FROM ps_customer LEFT JOIN ps_orders ON ps_customer.id_customer=ps_orders.id_customer LEFT JOIN ps_order_history ON
ps_order_history.id_order=ps_orders.id_order

WHERE payment='PayPal' and id_order_state=4

[joho]

Or if you want to search based on status = "Order shipped":

SELECT ps_customer.firstname, ps_customer.lastname
FROM ps_customer LEFT JOIN ps_orders ON ps_customer.id_customer=ps_orders.id_customer
LEFT JOIN ps_order_history ON ps_order_history.id_order=ps_orders.id_order

WHERE payment='PayPal' and id_order_state=4

But wouldn't that ignore the fact that an order could have changed state since?

Hmm.. re-reading the intial question, I guess he may not care, so I suppose I'm stating the obvious :-)

[noesac]

Or if you want to search based on status = "Order shipped":

SELECT ps_customer.firstname, ps_customer.lastname
FROM ps_customer LEFT JOIN ps_orders ON ps_customer.id_customer=ps_orders.id_customer
LEFT JOIN ps_order_history ON ps_order_history.id_order=ps_orders.id_order

WHERE payment='PayPal' and id_order_state=4

But wouldn't that ignore the fact that an order could have changed state since?

Hmm.. re-reading the intial question, I guess he may not care, so I suppose I'm stating the obvious :-)

I'm terrible at writing queries so if you have a better one I'd love to see it, it's a good learning opportunity for me too:)

[Thomas Robert]

The SELECT statement is used to select data from a database.
The result is stored in a result table, called the result-set.

[joho]

This will generate a list of all orders (id_order) with their latest/current status (from ps_order_history). You can then use that list to fetch the actual order data from other tables.

select * from ps_order_history
where id_order_history in 
(SELECT t1.id_order_history FROM ps_order_history t1
LEFT JOIN ps_order_history t2 ON t1.id_order = t2.id_order and t1.date_addwhere t2.id_order is null order by t1.id_order)
ORDER BY `ps_order_history`.`id_order`  ASC

If you want to do any type of filtering on which types of orders are returned, you add the WHERE to the outer query (not the sub-query). You may, for example, only wish to return orders with a given (current) status.