CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Connie DeCinko
Connie DeCinko

Reputation: 1036

Sub select with conditions

I have a sub query that gets the address for the person filing a complaint. The person can have multiple addresses. We used to just select the address of type 'OR' which makes for a clean subquery of...

(SELECT TOP 1 Address 
 FROM Address WHERE NameID = PersNameID AND AddressTypeCode = 'OR') AS ComplainantAddress

Now, the way the addresses are entered, they may or may not have an address of type 'OR'. They may only have an address of type 'ML' or of type 'BU' and 'ML'.

I need to get the first address that is populated for the person, looking in a specified order (by type). I could do a huge CASE statement but is that the best way? Or should I change to a JOIN with an ORDER By and take the first non null value?

Looking for suggestions.

Upvotes: 2

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You don't have to use a CASE, but you do have to do something similar. Here is a trick using CHARINDEX():

(SELECT TOP 1 Address 
 FROM Address
 WHERE NameID = PersNameID 
 ORDER BY CHARINDEX(AddressTypeCode, 'OR,ML,BU')
)

If you have additional codes and don't want them first, then:

(SELECT TOP 1 Address 
 FROM Address
 WHERE NameID = PersNameID 
 ORDER BY (CASE WHEN AddressTypeCode IN ('OR', 'ML', 'BU')
                THEN 1 ELSE 2
           END),
          CHARINDEX(AddressTypeCode, 'OR,ML,BU')
)

EDIT:

I should note there are more esoteric means of doing the same thing:

(SELECT TOP 1 Address 
 FROM Address LEFT OUTER JOIN
      (VALUES ('OR', 1), ('ML', 2), ('BU', 3)
      ) codes(code, priority)
      ON Address.AddressTypeCode = codes.code
 WHERE NameID = PersNameID 
 ORDER BY COALESCE(codes.priority, 999999)
)

This is more SQL-Server'ish. The string manipulation is something that works (with changes in function name) on almost any database.

Upvotes: 6

Tom H
Tom H

Reputation: 47464

If you're going to have a priority for your addresses then I would suggest capturing that in the database. So, assuming that your AddressTypes table has a ComplaintPriority column, you could then do this:

SELECT TOP 1
    A.Address
FROM
    Address A
INNER JOIN AddressTypes T ON T.AddressTypeCode = A.AddressTypeCode
WHERE
    A.NameID = PersNameID  -- Not sure where this is coming from
ORDER BY
    T.ComplaintPriority

Upvotes: 2

Related Questions