It seemed simple at the time... 1

[LNBruno]

I have a table that looks like this:

ID ReferenceNo IDtype
123456789 555-1111 EIN
234567890 555-1111 ITN
345678901 555-1111 SSN
456789012 555-2222 EIN
567890123 555-3333 SSN

The same reference number can have one or more IDtypes. What I want to do is to return a single ID based on this:

1) if there is an EIN, return that ID
2) if there is no EIN but there is an SSN, return that ID
3) if there is no EIN or SSN, but there is an ITN, return that ID
4) if none of the above, return NULL.

I know I'm going to hate myself when someone posts what I'm sure is going to be a very simple answer.

< M!ke >

 

[r937]

select ID         
     , ReferenceNo  
     , IDtype
  from daTable 
 where IDtype = 'EIN'
union all
select ID         
     , ReferenceNo  
     , IDtype
  from daTable as T
 where IDtype = 'SSN'
   and not exists
       ( select *
           from daTable
          where ReferenceNo = T.ReferenceNo
            and IDtype = 'EIN' )
union all
select ID         
     , ReferenceNo  
     , IDtype
  from daTable as T
 where IDtype = 'ITN'
   and not exists
       ( select *
           from daTable
          where ReferenceNo = T.ReferenceNo
            and IDtype in ('EIN','SSN') )
union all
select null         
     , ReferenceNo  
     , null
  from daTable as T
 where not exists
       ( select *
           from daTable
          where ReferenceNo = T.ReferenceNo
            and IDtype in ('EIN','SSN','ITN') )

r937.com | rudy.ca

 

[LNBruno]

Yep. I was right. I hate myself.

Thanks!

< M!ke >