Exclude a primary name if a preferred name is entered 3

[kernal]

I want to exclude a person's primary name if a preferred name is in a table. Thanks

type name id
primary Cynthia Doe 1
preferred Cyd Doe 1
primary William Doe 2
preferred John Doe 3

Needed results
type name id
preferred Cyd Doe 1
primary William Doe 2
preferred John Doe 3

 

[johnherman]

select type, name, id from table table where table.id in (select a.id from table a where a.type = 'preferred' union select b.id from table b where b.id not in (select c.id from table c where c.type = 'preferred')

untested, but you get the idea

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile innovative cognitive big data clouds)

 

[taupirho]

If you're on V9 use analytics instead, I think it will be faster than John's suggestion

select id,type,name
from
(
select count(*) over(partition by id order by type) cnt,id,type,name
from your_table
) where cnt = 1

In order to understand recursion, you must first understand recursion.

 

[Beilstwh]

Another way to do it is

select type, name, id
from my_table a
where not exists
(select null
from my_table b
where a.id = b.id
and b.type = 'preferred'
and a.type = 'primary');


Bill
Lead Application Developer
New York State, USA

 

[kernal]

Thanks everyone. I'm going to try all suggestions.

 

[kernal]

Thank you Bill. It worked BEAUTIFULLY!