CODEWITHSUNDEEP
sqlt-sql
Kenoyer130
Kenoyer130

Reputation: 7318

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 CHAR
)

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

Upvotes: 0

Views: 475

Answers (4)

HLGEM
HLGEM

Reputation: 96640

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')

Upvotes: 2

gbn
gbn

Reputation: 432667

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

Upvotes: 1

DOK
DOK

Reputation: 32851

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.

Upvotes: 1

XtremeBytes
XtremeBytes

Reputation: 1497

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

Upvotes: 1

Related Questions