Re: Help with SQL query

From: m.mueller <m.mueller_at_snafu.de>
Date: Fri, 03 Aug 2001 22:47:06 +0200
Message-ID: <3B6B0DCA.D3ABA190@snafu.de>

Hello Graham,
created the two tables, inserted just a few test rows to show the principle: create table faq_m(

       faq_id      number(8) constraint pk_faq_id primary key
       );
create table faq_d(
       faq_id	   number(8) constraint fk_faq_id references faq_m(faq_id),
       fname	   varchar2(10),
       lname	   varchar2(10),
       entry_date  date)

;
insert into faq_m values(1);
insert into faq_d (faq_id, fname, lname, entry_date) values(1, 'surname1', 'lname1', to_date('25-07-2001', 'DD-MM-YYYY')); insert into faq_d (faq_id, fname, lname, entry_date) values(1, 'surname1', 'lname1', to_date('25-07-2001', 'DD-MM-YYYY')); insert into faq_m values(2);
insert into faq_d (faq_id, fname, lname, entry_date) values(2, 'surname2', 'lname2', to_date('20-07-2001', 'DD-MM-YYYY')); insert into faq_d (faq_id, fname, lname, entry_date) values(2, 'surname2', 'lname2', to_date('25-07-2001', 'DD-MM-YYYY')); insert into faq_d (faq_id, fname, lname, entry_date) values(2, 'surname2', 'lname2', to_date('01-08-2001', 'DD-MM-YYYY')); insert into faq_m values(3);
insert into faq_d (faq_id, fname, lname, entry_date) values(3, 'surname3', 'lname3', to_date('10-07-2001', 'DD-MM-YYYY'));

select m.faq_id, max(d.fname), count(d.fname), d.lname, d.entry_date   from faq_m m, faq_d d
 where m.faq_id = d.faq_id
   and d.entry_date < sysdate - 8
group by m.faq_id, d.lname, d.entry_date;

    FAQ_ID MAX(D.FNAM COUNT(D.FNAME) LNAME ENTRY_DAT

---------- ---------- -------------- ---------- ---------
         1 surname1                2 lname1     25-JUL-01
         2 surname2                1 lname2     20-JUL-01
         2 surname2                1 lname2     25-JUL-01
         3 surname3                1 lname3     10-JUL-01

hope that helps
Manuela Mueller Received on Fri Aug 03 2001 - 15:47:06 CDT