One way is you can use a [window function](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15) and a [CTE](https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15) to check the value of all of the `totalAmount`s 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.