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 lost out the columns you want to SELECT, using SELECT * is bad practice for readability, maintainability, debugability, and even performance.

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.

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

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 lost out the columns you want to SELECT, using SELECT * is bad practice for readability, maintainability, debugability, and even performance.