How can I find records in a subquery result where an item has a result which is false and has no res-CodePudding

I'm pretty deep into a combination of joins and subqueries which have ended up having a query result which looks like this: I'm not dealing with marbles at all, but I guess a way of explaining this would be to imagine a bag with blue and red marbles. Bag 1 contains both blue and red, (hence a result for true and false), Bag 2 also the same. Bag 3 only only contains blue marbles and no red ones, so there is only one entry which is true. And bag 4 contains only red ones so the value is false.

What I'm trying to do is select all the IDs where they don't have a 'true' value. So in this case below, IDs 1,2 and 3 all have a row which has the value 'true', whereas IDs 4 and 5 only contains false(s) and no trues. So I want the query to return ID 4 and 5. These results are grouped by their IDs so I've been trying different HAVING clauses, trying to count where COUNT of trues = 0 for a specific idea but haven't had any success.

Input: this table (which is a result of a bigger query)

| ID | hasBlueMarbles   | 
| -- | ------- |
| 1  | true    |
| 1  | false   |
| 2  | true    |
| 2  | false   |
| 3  | true    |
| 4  | false   |
| 5  | false   |
| 5  | false   |

expected output: IDs which only have a record for the value 'false' and not 'true'

| ID | 
| -- |
| 4  |
| 5  |

This is probably quite a weird dataset to have in the first place, probably meaning I did something quite wrong with my initial queries that got me here. If needed I can add the main query but it may be a little confusing out of context. Thank you!

CodePudding user response：

1. BOOL_OR()

The function you are looking for is BOOL_OR().

Here explains as:

If any value in a set is true, the BOOL_OR function returns true (t). If no value in a set is true, the function returns false (f).

Here goes:

SELECT ID, 
       BOOL_OR(hasBlueMarbles) AS HasBlueMarbles
FROM table
GROUP BY ID
HAVING BOOL_OR(hasBlueMarbles) = FALSE

2. MAX()

Or you can simply use MAX() function to take the biggest of booleans and filter out the result.

SELECT ID, 
       MAX(hasBlueMarbles) AS HasBlueMarbles
FROM table
GROUP BY ID
HAVING MAX(hasBlueMarbles) = 0