Mysql subquery : Trying to get the last order from a customer

Question

Sorry , maybe it's trivial but I can't find a way to do this :

table customers

• customer_id
• customer_email
• customer_newsletter (1=yes/0=no)

table orders

• order_id
• customer_id
• order_status (1=ok/0=no_ok)

Each customer can have zero, one or many orders.

I'm trying to make a query to get all the emails of the persons who are :

1/ not yet customers, they have never placed an order but they want to receive my newsletter

or

2/ already customers (one or more orders in the corresponding table). But only if the status of their latest order is OK (I don't want to send the newsletter to problematic customers)

I've tried several things based on subqueries with MAX function but can't find the good formulation

Any advice ? thanks in advance

Answer 1

SELECT  customer_email
FROM    customers c
WHERE   customer_newsletter
        AND COALESCE(
        (
        SELECT  order_status
        FROM    orders o
        WHERE   o.customer_id = c.customer_id
        ORDER BY
                o.customer_id DESC, o.date_purchased DESC
        LIMIT 1
        ), 1)

Create an index on orders (customer_id, date_purchased) for this to work fast. However, it will work even without the index.

If you want the successful customers receive emails regardless of their newsletter settings, use this:

SELECT  customer_email
FROM    customers c
WHERE   COALESCE(
        (
        SELECT  order_status
        FROM    orders o
        WHERE   o.customer_id = c.customer_id
        ORDER BY
                o.customer_id DESC, o.date_purchased DESC
        LIMIT 1
        ), customer_newsletter)

Answer 2

To get customers without orders who want your newsletter you can use a LEFT OUTER JOIN.

Example (untested):

select c.*
from customers c left outer join orders o
on c.customer_id = o.customer_id
where (o.customer_id is null) and customer_newsletter = 1
order by c.customer_id

Answer 3

1) select * from customers c where customer_newsletter = 1 and not exists ( select * from orders o where c.customer_id = o.customer_id )

2) select * from customers c where 1 = ( select order_status from orders o1 where o1.customer_id = c.customer_id and o1.order_id = ( select max(order_id) from orders o2 where o2.customer_id = c.customer_id) )

Answer 4

Assuming you can use the greatest order id as the most recent, I think should work:

SELECT c.customer_email
FROM customers c
LEFT OUTER JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_newsletter = 1 AND o.customer_id IS NULL
UNION
SELECT c.customer_email
FROM customers c
WHERE customer_id IN 
  (SELECT customer_id FROM orders WHERE order_id IN 
    (SELECT MAX(order_id) FROM orders WHERE order_status = 1 GROUP BY customer_id)
  )