Okay - I've already said I'm not very good at this coding lark....

explain plan for
select *
from affected_cat_cust
where (hurn,purn,cat_id) in
(select hurn,purn,cat_id
from
(select hurn,purn,cat_id,count(*)
from affected_cat_cust
group by hurn,purn,cat_id
having count(*) = 1
)
)
and (hurn,purn,cat_id) not in(select hurn,purn,cat_id from affected_catalogue_customer)

-------------------------------------------------------

OPERATION OPTIONS OBJECT_NAME OPTIMIZER COST CARDINALITY
----------------------------------- ---------------- -------------------- ---------- --------- -----
SELECT STATEMENT CHOOSE 618222 5681
FILTER
HASH JOIN SEMI 4674 5681
TABLE ACCESS FULL AFFECTED_CAT_CUST ANALYZED 2018 142014
VIEW VW_NSO_1 2656 28403
FILTER
SORT GROUP BY 2656 28403
TABLE ACCESS FULL AFFECTED_CAT_CUST ANALYZED 2018 2840283
TABLE ACCESS FULL AFFECTED_CATALOGUE_C ANALYZED 108 11

------------------------------------------------------

I know the 'having count(*) = 1' is giving me a filter, and the 'not in' clause is giving me the other.

Can anyone re-write this to maintian the integrity of the statement, but remove the flamin' filters ???

Dapi - how about you ??

much obliged.

Horace.