Problem with advanced distinct SQL query

Question

Ok this one is realy tricky :D

i have a this table

bills_products:
- bill_id - product_id - action -
|    1    |      4     |   add  |
|    1    |      5     |   add  |
|    2    |      4     | remove |
|    2    |      1     |   add  |
|    3    |      4     |   add  |

as you can see product with the id 4 was added at bill 1 then removed in bill 2 and added again in bill 3

All Bills belong to a bill_group. But for the simplicity sake let's assume all the bills are in the same group.

Now i need a SQL Query that shows all the products that are currently added at this group.

In this example that would be 5, 1 and 4. If we would remove the bill with id 3 that would be 5 and 1

I've tried to do this with DISTINCT but it's not powerful enough or maybe I'm doing it wrong.

Answer 1

SELECT DISTINCT product_id FROM bills_products WHERE action = 'add';

Answer 2

This seems to work in SQL Server at least:

select  product_id
from    (
            select product_id,
                   sum((case when action='add' then 1 else -1 end)) as number
            from   bills_products
            group by product_id
        ) as counts
where   number > 0

Answer 3

GSto almost had it, but you have to ORDER BY bill_id DESC to ensure you get the latest records.

SELECT DISTINCT product_id FROM bills_products
WHERE action = 'add'
ORDER BY bill_id DESC;

(P.S. I think most people would say it's a best practice to have a timestamp column on tables like this where you need to be able to know what the "newest" row is. You can't always rely on ids only ascending.)

Answer 4

REMOVED BY AUTHOR