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
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
-Talk
Subject: RE: SQL to Retrieve One Record from Several Possible Records
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
,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
Just to offer an alternate.
Instead of those time consuming case statements and joins, you could
just add a new table to assign a sort order value to each of the
Types, such as you said the table would look like this
1 - RMA
2 - LOC
3 - H
Where the numeric value would be the type's ordering ID
, but sometimes that's enough.
- Mark
From: Alan Rother [mailto:[EMAIL PROTECTED]
Sent: Thu 6/28/2007 5:47 PM
To: CF-Talk
Subject: Re: SQL to Retrieve One Record from Several Possible Records
Just to offer an alternate.
Instead of those time consuming case
6 matches
Mail list logo
