List:General Discussion« Previous MessageNext Message »
From:James Harvard Date:January 5 2006 4:13pm
Subject:Re: SELECT help.
View as plain text  
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.
Thread
SELECT help.Richard Reina5 Jan
  • Re: SELECT help.Rhino5 Jan
    • Re: SELECT help.Richard Reina5 Jan
    • Re: SELECT help.SGreen5 Jan
      • Re: SELECT help.Richard Reina7 Jan
  • Re: SELECT help.Michael Stassen5 Jan
  • Re: SELECT help.James Harvard5 Jan
  • Re: SELECT help.Peter Brawley5 Jan