×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Exclude a primary name if a preferred name is entered
3

Exclude a primary name if a preferred name is entered

Exclude a primary name if a preferred name is entered

(OP)
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

RE: Exclude a primary name if a preferred name is entered

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)


RE: Exclude a primary name if a preferred name is entered

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.

RE: Exclude a primary name if a preferred name is entered

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

RE: Exclude a primary name if a preferred name is entered

(OP)
Thanks everyone. I'm going to try all suggestions.

RE: Exclude a primary name if a preferred name is entered

(OP)
Thank you Bill. It worked BEAUTIFULLY!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close