Problem solve Get help with specific problems with your technologies, process and projects.

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:

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

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
         end  as Type
     , case when work.PREFERRED='Y'
            then work.EMAIL_ADDRESS
            else coalesce(pers.EMAIL_ADDRESS
         end  as Email
     , 'A'    as Status
     , case when work.PREFERRED='Y'
            then work.PREFERRED
            else coalesce(pers.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?

Dig Deeper on Oracle and SQL

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.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.