oh... i love an sql challenge!
you need to do a left join back onto the orders table. i get a little confused as to whether its an inner join or an outer join. but you can use this as your starting point:
Code:
select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname, ct.ord_count from TABLE_CUSTOMERS c
join TABLE_ORDERS o on c.customers_id = o.customers_id
left join (select count(orders_id) as ord_count, customers_email_address from TABLE_ORDERS group by customers_email_address) ct on c.customers_email_address = ct.customers_email_address
WHERE c.customers_newsletter = 1 and ct.ord_count < 25
GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH)
ORDER BY c.customers_lastname, c.customers_firstname ASC
once you see what is going on there, i think the rest of your questions will answer themselves..... i hope!
good luck!
Bookmarks