CODEWITHSUNDEEP
sqlsql-servert-sql
Jim P.
Jim P.

Reputation: 1127

Please help form this T-SQL Query to return correct Account results

I have an accounts and a contacts table, and I am trying to find any accounts where NONE of the contacts associated with that account have a value for a certain field.

Scenario 1: Account WaffleHouse has 3 contacts, 1 of the contacts has a value in field "Field1". This account IS NOT returned in result set.

Scenario 2: Account PancakeHouse has 5 contacts, NONE of the contacts has a value in the field "Field1" set to TRUE. This account IS returned in result set.

I tried this code, and it is returning ANY account that has a contact where field is blank or null. 

select distinct a.accountid
from account as a
inner join contact as c
on a.accountid = c.accountid
where (c.Field1 is null or c.Field1 = '')

Upvotes: 0

Views: 42

Answers (3)

paparazzo
paparazzo

Reputation: 45096

select distinct a.accountid
  from account 
except
select c.accountid
  from contact c
 where c.Field1 is not null

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Try this:

SELECT  *
FROM    Accounts a
WHERE   NOT EXISTS ( SELECT *
                     FROM   contact c
                     WHERE  a.accountid = c.accountid
                            AND c.Field1 = 'TRUE' )

Or:

SELECT  *
FROM    Accounts a
WHERE   NOT EXISTS ( SELECT *
                     FROM   contact c
                     WHERE  a.accountid = c.accountid
                            AND c.Field1 <> '' )

Upvotes: 2

SlimsGhost
SlimsGhost

Reputation: 2909

You are very close - just use some aggregation (GROUP BY) and the MAX function to get what you want, like so:

select a.accountid
from account as a
inner join contact as c
on a.accountid = c.accountid
group by a.accountid
having MAX(isnull(c.Field1, '')) = ''

Upvotes: 1

Related Questions