Given I have a table of property ID's, ID's of the owner(s) of the properties and the owners' current cash balance, for example:

I need to return the property ID's where all of the linked owners have a minimum cash balance but not sure how to achieve this. If at least one linked owner doesn't have the minimum cash then that property is omitted from the results.

For example if the minimum cash balance that each owner would need to have is 50,000 the output would be:

To confirm, this is because owner_id 882 has less than 50,000 cash and therefore property_id 102 doesn't qualify.

What would be the best way to achieve this? Using Microsoft SQL Server.

I've looked into recursive CTE's and potentially using a WHILE loop to get desired result but can't put them into practice here.

CodePudding user response：

You can utilise not exists here, you want the Property_Ids where there does not exist and matching property_Id with owner_cash < 50,000

select distinct property_id 
from t
where not exists (
    select * from t t2
    where t2.property_id = t.property_id and t2.owner_cash < 50000
);