How do you get results where all properties under the same client are inactive but the client isn&#0-CodePudding

I have two tables, one with Client Address information called ClientData, and one with information on their properties called PropertyData. In both tables, there is a Status field to indicate whether or not the client is inactive and both tables have a field called ClientID to indicate which client the property belongs to. I'm trying to figure out a query that will find clients where ALL of the properties under the same ClientID have PropertyData.Status ='INACTIVE' but ClientData.Status isn't marked INACTIVE.

Sorry if my question is unclear. This is my first time using Stack Overflow.

Haven't figured out any queries on this yet.

CodePudding user response：

SELECT DISTINCT ClientData.ClientID
FROM ClientData
JOIN PropertyData ON ClientData.ClientID = PropertyData.ClientID
WHERE NOT EXISTS (
    SELECT 1 FROM PropertyData
    WHERE PropertyData.ClientID = ClientData.ClientID
    AND PropertyData.Status != 'INACTIVE')
AND ClientData.Status != 'INACTIVE'