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
Join the conversationComment
Share
Comments
Results
Contribute to the conversation