CODEWITHSUNDEEP
sql-servert-sql
Duston
Duston

Reputation: 1641

T-SQL return a row if another row doesn't exist

Let's say I have this table (this is simplified, of course there are other columns):

CompanyID (int)
ContactName (varchar(50))
ContactType (char(1))

with the values:

CompanyID | ContactName           | ContactType
-----------------------------------------------
1         | John Doe              |   A
1         | Jane Smith            |   B
2         | Ralph Jones           |   B
3         | Dick Grayson          |   A

What I want are all the companies where there's a ContactType='A', unless there is no ContactType='A' return the ContactType='B'. So in this example, I want:

1, John Doe (because he's a ContactType A)
2, Ralph Jones (because Company #2 doesn't have a ContactType A)
3, Dick Grayson (because he's a ContactType A)

I can't just say "A or B" because a company may have both.

Here's what I tried (and failed)

use MyFancyDatabase
drop table #TypeA
drop table #TypeB
drop table #TypeAB

create table #TypeA(ownerkey int, ContactName varchar(200), ContactType char(1))
insert #TypeA
Select ownerkey, ContactName, ContactType from address
where ContactType = 'A' and CancelDate is null

create table #TypeB(ownerkey int, ContactName varchar(200), ContactType char(1))
insert #TypeB
Select ownerkey, ContactName, ContactType from address
where ContactType = 'B' and CancelDate is null

create table #TypeAB(ownerkey int, ContactName varchar(200), ContactType char(1))
insert #TypeAB

select * from #TypeA
except
select * from #TypeB

I guess in English it's "A, but if there is no A, then take B."

Any suggestions?

Upvotes: 1

Views: 69

Answers (4)

Alex Fletcher
Alex Fletcher

Reputation: 56

The columns in your defined table don't appear to match with the ones in your example query. I'm guessing OwnerKey is the same as CompanyID?

If so, and you keep most of the rest of your code, the last select would need to be:

select * from #TypeA
union all
select * from #TypeB
where not exists (
    select * 
    from #TypeA
    where #TypeA.ownerkey = #TypeB.ownerkey)

Upvotes: 0

Y. M.
Y. M.

Reputation: 107

Try this SQL

Select t1.CompanyID , 
       ContactName =  IIF(t1.ContactType='A',t1.ContactName,t2.ContactName)
       ContactType =  IIF(t1.ContactType='A','A',t2.ContactType)
FROM address as t1 left join address as t2 
   on t1.CompanyID = t2.CompanyID 
     AND t1.ContactName = t2.ContactName
     AND (t1.ContactType <> t2.ContactType)

IF you have more type than A OR B and you want just A and B add this on the where statment 

WHERE  (t1.ContactType = 'A' OR t1.ContactType = 'B')
   AND (t2.ContactType = 'A' OR t2.ContactType = 'B')

Upvotes: 0

Maverick
Maverick

Reputation: 1185

SELECT a.OwnerKey, a.CompanyName, Case WHEN a.ContactType IS NULL THEN b.ContactType ELSE a.ContactType END AS ContactType
FROM #TypeA a
LEFT JOIN #TypeB b on a.OwnerKey = b.OwnerKey

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33571

I think this should work for you.

with SortedResults as
(
    select CompanyID
        , ContactName
        , ContactType
        , ROW_NUMBER() over (partition by CompanyID order by ContactType) as RowNum
    from ThisTable
)

select *
from SortedResults
where RowNum = 1

Upvotes: 1

Related Questions