Eliminate records by grouping and aggregation

Question

I have a temporary table with the following content:

playlist_id | item_id | passed
----------------------------------------
 123 | 111 | true
 123 | 111 | false
 123 | 111 | true
 123 | 112 | true
 456 | 212 | false
 789 | 212 | true

I need to reduce the results, if for a playlist_id, item_id, I need to keep it only if all the passed values ​​are true, so in In this example, the result I want is:

playlist_id | item_id | passed
----------------------------------------
 123 | 112 | true
 789 | 212 | true

Because the second record's playlist_id, item_id pair has a false value, the entire corresponding group needs to be deleted. I tried using group by and having, so the query is:

select
  playlist,
  item_id
from
  temp table
group by
  playlist_id,
  item_id
having passed = true

But this returns me all pairs that have at least one true value.

How do I eliminate all playlist_id, item_id records if any of the boolean passed fields is false?

Thank you!

Answer 1

You need to use aggregate values ​​in HAVING. Otherwise, you're just testing a random row in each group.

Use MIN(passed) to get the minimum value of passed in each group. 1 if all values ​​are true, or 0 if any false values ​​are present.

SELECT playlist, item_id
FROM temp_table
GROUP BY playlist, item_id
HAVING MIN(passed) = true