Use TSQL to select value B if A is not found

We have an address table where the addresses for an account are marked Primary or Shipping.

We want to select the Shipping address unless there is no shipping address, in which case we want the Primary address.

Whats the best approach using TSQL on SqlServer 2005?

In the below sample, querying for ID 1 or 2 should return the S record. Querying for ID 2 should return the P record.

Note there are other possible address types that should be ignored.

DECLARE @tmp TABLE(
 ID int,
 AddressType CHA开发者_JS百科R
)

INSERT INTO @tmp (ID,addresstype) VALUES (1,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (1,'S')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'P')
INSERT INTO @tmp (ID,addresstype) VALUES (2,'A')

SELECT * from @tmp

This is nasty but it works:

 select  distinct t1.id,coalesce (t2.addresstype, t3.addresstype)
 from @tmp t1
 left join @tmp t2 on t1.id = t2.id and  t2.addresstype = 'S'
 left join @tmp t3 on t1.id = t3.id and t3.addresstype = 'P' 
 where t1.addresstype  in ('P', 'S')

Assuming you want to choose between P or S

Select Top 1 * from @tmp Where AddressType In( 'P', 'S') And ID = @id Order By AddressType Desc

Are you looking for COALESCE? It returns the first non-null expression among its arguments.

For example, SELECT COALESCE(ShippingAddress, PrimaryAddress) FROM tableName.... retrieves ShippingAddress if it is not NULL, and if ShippingAddress is NULL, it returns PrimaryAddress, unless PrimaryAddress is also NULL, in which case it just returns NULL.

Here is an example page that might be helpful. And the MSDN page.

Instead of fancy code, just rely on S > P in a set limited to P and S

SELECT
    ID, MAX(AddressType)
FROM
    @tmp
WHERE 
    AddressType IN ('P', 'S')
GROUP BY
    ID

You can use ROW_NUMBER or a UNION or a derived table too.

If you want for one ID only, then TOP is easier

SELECT TOP 1
    ID, AddressType
FROM
    @tmp
WHERE 
    AddressType IN ('P', 'S')
    AND
    ID = 2  --1
ORDER BY
    AddressType DESC

It depends on your usage: one ID or many IDs in one query