How to return instances where all members of GROUP BY equal a certain value?-CodePudding

I have the following table structure:

Essentially, I need to return the rows where ALL VALUES of that grouping are FALSE for LeadIndication. I am using GROUP BY on the Parent column but having trouble getting the instances where ALL records in the GROUP BY are FALSE. Below is my attempt which return all groupings by "Parent" but having trouble layering in the additional logic.

SELECT [AssetID], [InvestmentID] FROM [rdInvestments] GROUP BY [AssetID],[InvestmentID]

As you can see based the yellow highligthed portion of my screen shot, I only want to return those rows since ALL members of the GROUP BY are false for LeadIndication.

CodePudding user response：

Using conditional aggregation:

SELECT AssetID
FROM rdInvestments
GROUP BY AssetID
HAVING SUM(IIF(LeadIndication <> 'FALSE', 1, 0)) = 0;

Another way:

SELECT AssetID
FROM rdInvestments
GROUP BY AssetID
HAVING SUM(IIF(LeadIndication = 'FALSE', 1, 0)) = COUNT(*);

CodePudding user response：

Try this using a subquery:

SELECT DISTINCT
    [AssetID]
FROM 
    [rdInvestments]
WHERE
    [AssetID] Not In
        (SELECT T.[AssetID]
        FROM [rdInvestments] As T
        WHERE T.LeadIndication = 'TRUE')