Search code examples
sqlgoogle-cloud-spanner

Select rows with all childrens with only one status

I look for some help with creation of SQL script. I have such data:

table 'plans' with 'plan_id' table 'transactions' with 'transaction_status'

plans and transactions are one-to-many, means that plans can have a lot of transactions

I want to read all plan_ids, but only such which has 100% of SUCCESS status

My real scenario is more complex than this, but I am left with such problem to solve now.

Solution

  • Below I post query example for PostgreSQL. You can adopt it to your version:

    SELECT 
    plans.id, 
    COUNT(plan_id) all_transactions, 
    SUM(CASE WHEN success THEN 1 ELSE 0 END) success_transactions
FROM plans
JOIN transactions ON transactions.plan_id = plans.id
GROUP BY plans.id
HAVING COUNT(plan_id) = SUM(CASE WHEN success THEN 1 ELSE 0 END)
;

    PostgreSQL fiddle