You could do multiple joins and check for null in your case statement.
(this is assuming you have multiple addresses in a separate table with a
primary key somehow relating to your main user database. In my example
I am joining your pSeason file with the alias A to my theoretical
pAddresses file with alias B against B.seasonID. Then I join it using a
left join 2 more times. If a left join returns no record, it will
return NULL when checking for that field.) Hope this helps and hope it
makes sense!
SELECT
pseason.id
,pseason.addr_type
,pseason.person_addresses
,CASE
WHEN A.addr_type IS NOT NULL THEN A.myAddressField
WHEN A.addr_type IS NULL AND B.addr_type IS NOT NULL THEN
B.myAddressField
WHEN A.addr_type IS NULL AND B.addr_type IS NULL THEN
C.myAddressField
ELSE 'No Address Found'
END AS SeasonAddress
FROM
Pseason A LEFT JOIN Paddresses B ON A.id = B.seasonID
AND B.addr_type = 'H'
LEFT JOIN Paddresses C ON A.id = C.seasonID
AND C.addr_type = 'AM'
LEFT JOIN Paddresses D ON A.id = D.seasonID
AND D.addr_type = 'ISIR'
WHERE
pseason.id = '0000133'
Chris Peterson
Gainey IT
Adobe Certified Advanced Coldfusion Developer
-----------------------------------------------------------------------
From: Dawson, Michael
Sent: Thursday, June 28, 2007 2:56 PM
To: CF-Talk
Subject: SQL to Retrieve One Record from Several Possible Records
I am trying to select records based on a precedence. I'm using SQL2005.
These records are address records for our students and employees. Each
person will have, at least, one address, but many people will have more
than one address.
The addresses are coded so that we know the type of address: H = Home,
LOC = Local, RMA = Room Assignment.
I want to pull only one record based on a precedence. For example, if I
find an RMA record, then I want to pull that and no other records. If
there is no RMA record, I need to find a LOC record, if one exists.
Repeat as needed until I find that last address type.
The precedence is:
1 - RMA
2 - LOC
3 - H
Here is the first step to find the address type in the order I need:
SELECT DISTINCT TOP 1
pseason.id
,pseason.addr_type
,pseason.person_addresses
,CASE
WHEN pseason.addr_type = 'H' THEN 1
WHEN pseason.addr_type = 'AM' THEN 2
WHEN pseason.addr_type = 'ISIR' THEN 3
ELSE 999
END AS sortOrder
FROM
PSeason
WHERE
pseason.id = '0000133'
ORDER BY
sortOrder
Is there a better way to select these records?
Thanks
M!ke
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Upgrade to Adobe ColdFusion MX7
The most significant release in over 10 years. Upgrade & see new features.
http://www.adobe.com/products/coldfusion?sdid=RVJR
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:282494
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
