This should work: select c_name, count(t1.id) as t_count from customers c inner join transactions t1 on c.c_no = t1.c_no left join transactions t2 on c.c_no = t2.c_no and t2.date > '2005-06-05' where t2.id is null group by c.c_no having t_count > 4;
There may be more efficient way of doing this though, if your tables are very large. This might work too: select c_name, count(t.id) as t_count, max(t.date) as t_latest from customers c inner join transactions t on c.c_no = t.c_no group by c.c_no having t_count > 4 and t_latest < '2005-06-05'; HTH, James Harvard At 7:29 am -0800 5/1/06, Richard Reina wrote: >Can someone help me write a query to tell me the customer numbers (C_NO) of >those who've had more than 4 transactions but none in the last 6 months? > > | transactions_table | > | ID | C_NO | DATE | AMOUT| > |2901 | 387 |"2003-10-09" | 23.00| > > Obviously my table has many more entries. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
