how to do lookup and filter data on the same table in Oracle SQL

Question

In SQL, where we need to filter the unnecessary data from a table:

• case 1: if 2 IDs are same and DOD is not null then Record is needed

• case 2: if single id is there and dod is not null then Record is needed

• case 3: if 2 ids are same and if DOD is null for any one of them ,then record is not needed

Your help is much appreciated.

Thanks

Answer 1

You can use analytic functions for this:

select t.*
from (
    select 
        t.*, 
        sum(case when dod is null then 1 else 0 end) over(partition by id) no_nulls
    from mytable t
) t
where no_nulls = 0

Note that this also excludes records that have no duplicate id but whose dod is null (you did not describe how to handle those).

You could also use not exists (which can conviniently be turned to a delete statement if needed):

select t.*
from mytable t
where not exists(select 1 from mytable t1 where t1.id = t.id and t1.dod is null)
where no_nulls = 0