I get what you mean ...

Created a table to see the results.

Supposed i have a table table_t:

ID OCDATE TC

1 9/26/2006 CB
1 9/26/2006 UT
2 9/26/2006 CS
2 9/26/2006 UT
3 9/26/2006 CS
4 9/26/2006 CB
5 9/26/2006 CS
4 9/26/2006 SS
5 9/26/2006 SS
6 9/26/2006 SS
7 9/26/2006 SS

Select id from table_t where
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')

ID

6
7
5
4

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')

ID

1
1
2
2
3
4
5
4
5
6
7

select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)

ID

1
1
2
2
3

Is there a way to avoid 2 table scans on table_t for this query?
select id
from
table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and id not in (
select distinct id from table_t
where
ocdate >= to_date('9/26/2006','mm/dd/yyyy')
and ocdate <= to_date('9/27/2007','mm/dd/yyyy')
and tc not in ('CB', 'CS', 'UT')
)

Thanks abhaysk for pointing out my mistake!