2

I have the following table structure: 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.

chrtak
  • 43
  • 6

2 Answers2

0

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(*);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you, Tim. That doesn't seem to work though. This is MS Access so I needed to move to a SWITCH statement but it returns every record. ``` SELECT AssetID FROM rdInvestments GROUP BY AssetID HAVING COUNT( SWITCH(LeadIndication <> 'FALSE',1) ) = 0; ``` – chrtak Nov 28 '21 at 10:56
  • @SIMMS7400 Sorry...I must have missed the Access tag. I have fixed the answer. – Tim Biegeleisen Nov 28 '21 at 11:05
  • thanks for the edits! Unfortunately, that still doesn't work. It returns all records from the table even though I have explicitly set certain groupings to ALL false. – chrtak Nov 28 '21 at 11:06
  • Is `LeadIndication` a text/string column? The logic I have above should be working. I might suggest double checking your data. – Tim Biegeleisen Nov 28 '21 at 11:08
  • Hi @Tim, yes, LeadIndication is a string column. As is everything else. – chrtak Nov 28 '21 at 11:10
  • Try `SELECT AssetID, SUM(IIF(LeadIndication <> 'FALSE', 1, 0)) AS cnt FROM rdInvestments GROUP BY AssetID` ... what counts do you see? For the non zero counts, do you see non FALSE lead indication values? – Tim Biegeleisen Nov 28 '21 at 11:15
  • HI Tim, this is now working!! Sorry, I had a brain fart on my end. Works like a charm, thanks! – chrtak Nov 29 '21 at 13:50
0

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')
Gustav
  • 53,498
  • 7
  • 29
  • 55