CODEWITHSUNDEEP
sqloracle-database
dscl
dscl

Reputation: 1626

Oracle/SQL - Finding records with one value excluding by similar record

Okay let me do a better job explaining this. Assume I have a person column and a type column. The same person could be in the table multiple times, but with different types. I want all the people who have a specified type UNLESS they are listed with other types.

So given this data

Person  Type
--------------
Bob     S
Sue     S
Bob     O
Tom     S
Frank   S
Frank   R

I want to see Persons who have the Type S, but are also not listed with either Type O or R. So my query should return

Person  Type
--------------
Sue     S
Tom     S

Thanks!

Upvotes: 1

Views: 4188

Answers (7)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

I like Gary's option for performance reasons, but a simpler method is:

SELECT Person FROM mytable WHERE Type = 'S'
MINUS
SELECT Person FROM mytable WHERE Type IN ('O','R');

Upvotes: 0

Gary Myers
Gary Myers

Reputation: 35401

This will do:

SELECT person
FROM table
GROUP BY person
HAVING MIN(type) = 'S' AND MAX(type) = 'S'

However if you have multiple records for the same person and type 'S', then this will remove those duplicates

Upvotes: 2

Lost in Alabama
Lost in Alabama

Reputation: 1653

Maybe something like this:

select distinct Person, Type
from table
where (Person, type) in
  (select distinct Person, Type
   from table
   having count(*) = 1)

Added distincts to handle your case.

Upvotes: 0

Michael Broughton
Michael Broughton

Reputation: 4055

As an option to the NOT IN syntax, an outer join could handle that too

select t1.person, t1.type    
from   person_Type t1, person_type t2
where  t1.person = t2.person(+)
and    t1.type != t2.type(+)
and    t1.type = 'S'
and    t2.type is null;

Or, in response to the comment - for those who prefer the ANSI syntax...

select t1.person, t1.type
from  person_type t1
left outer join person_type t2
on  t2.person = t1.person
and t2.type != t1.type
where t1.type = 'S'
and t2.type is null;

Upvotes: 0

p.campbell
p.campbell

Reputation: 100557

How about this:

SELECT  person, type 
FROM      MyTable   t1 
WHERE (
        SELECT COUNT( DISTINCT  type ) 
        FROM @MyTable t2 
        WHERE t2.person = t1.person 
        AND  t1.Type='s'
        ) = 1
GROUP BY person, type

Upvotes: 0

Furkan
Furkan

Reputation: 460

SELECT DISTINCT person, ptype FROM persons p1 
WHERE (SELECT COUNT( DISTINCT ptype ) 
FROM persons p2 
WHERE p2.person = p1.person) = 1;

Upvotes: 0

SarekOfVulcan
SarekOfVulcan

Reputation: 1358

select person, type
    from myTable
    where type = 'S'
        and person NOT IN (
            select person
                from myTable
                where type in ('O', 'R')
        )

Upvotes: 0

Related Questions