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

gn (AT) dana (DOT) ucc.nau.edu schrieb:

scott@ORA102> create table t(id number,sent varchar2(1),sent_date date);

Table created.

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

1 row created.

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

1 row created.

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

1 row created.

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

1 row created.

scott@ORA102> prompt FIRST QUERY
FIRST QUERY
scott@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@ORA102> prompt SECOND QUERY
SECOND QUERY
scott@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@ORA102>


Best regards

Maxim

gn (AT) dana (DOT) ucc.nau.edu wrote:
The first thing you do is fix the structure of the table.
Three columns with 2 of the 3 reserved words.

SELECT keyword
FROM v$reserved_words
WHERE keyword IN ('ID', 'SENT', 'DATE');

This is obviously school work so you should ask your instructor.
If you write something that doesn't we will help you with hints
but we don't do other people's homework.

Daniel Morgan
www.psoug.org

It is not homework, I am an application systems analyst and was a
little unsure about part of a query, that was sudo code and a sudo
table not the actual column names, just a very simplified
representation. If you can't be bothered to assist people that's fine,
don't post at all!

Thank you so much, exactly what I needed

Have a great day!

gn (AT) dana (DOT) ucc.nau.edu wrote:
Excuse me but I would have thought that a systems analyst
would not have made mistakes more in line with those of my
first quarter students ... using reserved words for names
and not knowing how to start writing a query.

Daniel Morgan
www.psoug.org

I will take your comments as constructive.

Thanks for your time

Regards