Q

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.

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close