SQL Query to remove associated records that all don't meet certain condition

Question

I have the following tables and values in a 2014 SQL Express Server. They have been simplified to focus directly on the issue I'm having and for brevity.

Tbl_Contact
-ContactID

Tbl_Opportunity
-OpportunityID
-totalAmount

Tbl_Contact_Opportunity
-ContactID
-OpportunityID

This is the query I started with:

select * from Tbl_Contact
inner join Tbl_Contact_Opportunity on Tbl_Contact.ContactID = Tbl_Contact_Opportunity.ContactID
inner join Tbl_Opportunity on Tbl_Contact_Opportunity.OpportunityID = Tbl_Opportunity.Opportunity.ID 
where Tbl_Opportunity.totalAmount=0

This query returns a list of all the Tbl_Contact.ContactIDs that have a Tbl_Opportunity with a totalAmount=0. But I don't want that.

What I want to is to write a SQL query to list all the Tbl_Contact.ContactIDs that have all of their corresponding Tbl_Opportunitys with a totalAmount=0. I don't know how to do this.

For example take the following tables:

Tbl_Contact

ContactID
1
2

Tbl_Opportunity

OpportunityID	totalAmout
1	321
2	0
3	0
4	0

Tbl_Contact_Opportunity

ContactID	OpportunityID
1	1
1	2
2	3
2	4

with the query I wrote above the result would be:

ContactID
1
2

but the result I really want is:

ContactID
2

That is because both of the Tbl_Opportunitys associated with ContactID=2 have the totalAmount=0. Where ContactID=1 doesn't have both Tbl_Opportunitys associated with it having value totalAmount=0. One of them is 123, so I don't want that included in the query result.

Can someone help me rewrite my query to make this work?

Answer 1

Since you seem to want only ContactID in the output and only distinct values thereof, it is enough to employ regular grouping and aggregation for this and use just the tables Tbl_Opportunity and Tbl_Contact_Opportunity, as they have all the required data.

Your query would be as simple as this:

SELECT
  co.ContactID
FROM
  dbo.Tbl_Contact_Opportunity AS co
  INNER JOIN dbo.Tbl_Opportunity AS o ON co.OpportunityID = o.OpportunityID
GROUP BY
  co.ContactID
HAVING
  COUNT(CASE WHEN o.totalAmount <> 0 THEN 1 END) = 0
  -- another option: COUNT(NULLIF(o.totalAmount, 0)) = 0
;

The COUNT function above will count only the rows that have a non-zero totalAmount, giving you the number of such rows per group, i.e. per ContactID, since ContactID is the grouping term. You want contacts without non-zero amounts, therefore you just match the result of the COUNT to 0 in the HAVING clause to give you corresponding groups – or ContactID values.

Answer 2

One way is you can use a window function and a CTE to check the value of all of the totalAmounts of each partition of ContactID for any non-0 values like so:

WITH _ContactAmounts AS
(
    select *,
        MAX
        (
           CASE
               WHEN Tbl_Opportunity.totalAmount <> 0 THEN 1
               ELSE 0
           END
        ) 
        OVER (PARTITION BY Tbl_Contact.ContactID) AS HasNonZeroAmount
    from Tbl_Contact 
    inner join Tbl_Contact_Opportunity 
        on Tbl_Contact.ContactID = Tbl_Contact_Opportunity.ContactID 
    inner join Tbl_Opportunity 
        on Tbl_Contact_Opportunity.OpportunityID = Tbl_Opportunity.Opportunity.ID
)

SELECT *
FROM _ContactAmounts
WHERE HasNonZeroAmount = 0 -- Filters out all of ContactID rows who had at least one row with a non-0 totalAmount for that ContactID

Note you should also explicitly list out the columns you want to SELECT, using SELECT * is bad practice for readability, maintainability, debugability, and even performance.