Sql if one row has a value then don't display rest

I have customers that have more than one contract or none. I only want to show customers that have either no contract or contracts that are not active (A) but also have a balance. My current query selects these but I want to exclude the customer completely if they have even just one active contract.

SELECT T0.balance, *
FROM cust t0
left outer join Contracts t1 on t1.U_Code = t0.Code
Where (t1.U_Status != 'A'  or t1.U_Status is NULL) and t0.balance != 0 and CardType = 'c'
order by t0.balance

Sample Data

Results Desired (since A2 has an active we don't care):

Answers

To achieve the desired results, you can use a subquery to filter out customers who have at least one active contract. Then, you can join this subquery with the cust table to select customers with either no contracts or contracts that are not active but also have a balance. Here's how you can modify your query:

SELECT T0.balance, T0.Cust, T0.U_Status
FROM cust T0
LEFT JOIN (
    SELECT U_Code
    FROM Contracts
    WHERE U_Status = 'A'  -- Select active contracts
) AS ActiveContracts ON ActiveContracts.U_Code = T0.Code
WHERE (T0.balance != 0)  -- Customers with non-zero balance
    AND (T0.CardType = 'c')  -- Customers with CardType 'c'
    AND ActiveContracts.U_Code IS NULL  -- Customers with no active contracts
ORDER BY T0.balance;

In this query:

• The subquery selects the U_Code (customer code) from the Contracts table where the status is 'A' (active contracts).
• The main query then left joins the cust table with this subquery on the customer code.
• The WHERE clause filters for customers with a non-zero balance, CardType 'c', and no active contracts by checking for NULL values in the joined subquery.
• Finally, the results are ordered by balance.