Re: Query help

gn_at_dana.ucc.nau.edu schrieb:
> I am not sure how to write the following query:
>
> Simplified table structure:
>
> ID SENT DATE
> 123 Y 5/1/2006
> 124 N 5/2/2006
> 124 Y 5/1/2006
> 125 N 5/2/2006
>
> I want to return a row if SENT = 'N' AND also that ID has NEVER had a
> 'Y'
>
> So in the above example the only row I would want returned would be ID
> = 125
>
> The table structure and query are much more complicated than that but
> that is the part that I am unsure about.
>
> All help appreciated, thanks
>

scott_at_ORA102> create table t(id number,sent varchar2(1),sent_date date);

Table created.

scott_at_ORA102> insert into t values(123,'Y',DATE '2006-05-01');

1 row created.

scott_at_ORA102> insert into t values(124,'N',DATE '2006-05-02');

1 row created.

scott_at_ORA102> insert into t values(124,'Y',DATE '2006-05-01');

1 row created.

scott_at_ORA102> insert into t values(125,'N',DATE '2006-05-02');

1 row created.

scott_at_ORA102> prompt FIRST QUERY
FIRST QUERY
scott_at_ORA102> select * from t t_out

   2 where sent='N'
   3 and not exists
   4 ( select null from t t_in where t_out.id=t_in.id and sent='Y');

         ID SEN SENT_DATE

---------- --- -------------------
        125 N   02.05.2006 00:00:00

scott_at_ORA102> prompt SECOND QUERY
SECOND QUERY
scott_at_ORA102> select * from t

   2 where id in (
   3 select id from t where sent='N'
   4 minus
   5 select id from t where sent='Y'
   6 );

         ID SEN SENT_DATE

---------- --- -------------------
        125 N   02.05.2006 00:00:00

scott_at_ORA102>

Best regards

Maxim Received on Tue May 02 2006 - 13:12:13 CDT