CODEWITHSUNDEEP
sqloracle-database
Ryan Nalls
Ryan Nalls

Reputation: 23

Oracle/SQL - Finding records with one value excluding nulls

I have a question very similar to another question but I can't quite figure it out. Here is the link to the original question:Oracle/SQL - Finding records with one value excluding by similar record

So similar to that problem, I have records that will either have a 1 or null. the same records can be a combination of 1 or null and in those instances, I want to exclude the record altogether. For example:

    Person  Type
    --------------
    Bob     1
    Sue     1
    Bob     null
    Tom     1
    Frank   1
    Frank   null
    Fred    null

I want the following returned:

    Person  Type
    --------------
    Sue     1
    Tom     1

Any direction on this would be very much appreciated. I dont have much time to solve this so even speaking conceptually will help!

The closest I came was

    select person from table
    where type = 'S'
    MINUS
    select person from table
    where type is null

But of course that doesnt work.

I can write a function if that is the only way. Thank you!

Upvotes: 1

Views: 69

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You can readily do this with analytic functions, and these often offer very good performance:

select p.*
from (select p.*,
             sum(case when type is null then 1 else 0 end) over (partition by person) as Nullcnt
      from person p
     ) p
where nullcnt = 0;

Upvotes: 1

user330315
user330315

Reputation:

Apart from Mark's NOT IN approach, this can also be written as a NOT EXISTS condition:

select p1.person
from person p1
where p1.type = 1
and not exists (select 1 
                from person p2
                where p1.person = p2.person
                and p2.type is null)
order by p1.person;

It essentially says: get me every person where type is 1 but where there is no other row for this person where the type is null.

SQLFiddle example: http://sqlfiddle.com/#!4/7623c/4

Upvotes: 2

Mark
Mark

Reputation: 76

Try this:

select person, type from table
where type = '1'
and person not in (select person from table where type is null)

Upvotes: 2

Related Questions