Ask the Expert

Personal, work, and preferred e-mail addresses

I need to write a query that will select one e-mail address per person. The emails table is like this:

ID TYPE EMAIL_ADDRESS STATUS PREFERRED
1  PERS  asld@asdf      A       Y
1  WORK  asld@asdf      A       N
2  WORK  asld@asdf      A       N
3  PERS  asld@asdf      A       N
3  WORK  asld@asdf      A       Y
4  PERS  asld@asdf      A       N
4  WORK  asld@asdf      A       N

If the person has a preferred address I want that one, if not I want the PERS address and if they don't have a PERS then I need the WORK address. So from above I would want

ID TYPE EMAIL_ADDRESS STATUS PREFERRED
1  PERS  asld@asdf      A       Y
2  WORK  asld@asdf      A       N
3  WORK  asld@asdf      A       Y
4  PERS  asld@asdf      A       N

I can use a subquery with a min() function to select the PERS over the WORK, but I can not figure how to pull in the preferred.

    Requires Free Membership to View

From your explanation, it's safe to assume that there will always be a WORK address for every person. This WORK address may or may not be the preferred address. Each person might also have a PERS address, which may or may not be the preferred address, and it's probably also safe to assume that only one of the two can be preferred. If this is correct, then you can get exactly what you want like this:

select work.ID 
     , case when work.PREFERRED='Y'
            then work.TYPE
            else coalesce(pers.TYPE
                         ,work.TYPE)
         end  as Type
     , case when work.PREFERRED='Y'
            then work.EMAIL_ADDRESS
            else coalesce(pers.EMAIL_ADDRESS
                         ,work.EMAIL_ADDRESS)
         end  as Email
     , 'A'    as Status
     , case when work.PREFERRED='Y'
            then work.PREFERRED
            else coalesce(pers.PREFERRED
                         ,work.PREFERRED)
         end  as Preferred
  from emails as work
left outer
  join emails as pers
    on work.ID = pers.ID
   and pers.TYPE = 'PERS' 
   and pers.STATUS = 'A'
 where work.TYPE = 'WORK' 
   and work.STATUS = 'A'

Presumably you wanted STATUS to be 'A' (active) for both the WORK and PERS emails. You didn't mention this, but I've added the conditions to the JOIN and WHERE clauses just in case. And since both WORK and PERS have to be 'A', the SELECT just displays 'A'.

Each column is determined as follows: if the WORK row is preferred, choose the value from that row, otherwise choose the PERS value if it exists over the WORK value.

Neat, eh?

This was first published in April 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: