Help with a SQL Query for Query Builder

I'm trying to create a SQL query to add to the Query Builder table and need some assistance. Been running in circles for awhile :wacko:

I'm looking for a query very close to "Customer has placed <X orders and has not placed any orders in Y Amount of time".

The second part I've been attempting to utilize from the existing queries. Specifically:

Code:

---

select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 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

---

It is the "<X Orders" part I can't get. My attempts to integrate the COUNT function have failed.

And if someone manages that, maybe the following add on: "Customer has placed <X orders and has ordered product X and has not placed any orders in Y Amount of time".

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!

"Samsonite! I was way off!" - Yeah even my use off Count was nowhere close. Thanks!

Is vinsrare.com a site you consult for or are you NJ based? (I'm in NJ as well)