Eliminating matching values in a SQL result set
- by Burgess Taylor
I have a table with a list of transactions (invoices and credits) and I need to get a list of all the rows where the invoices and credits don't match up.
eg
user product value
bill ThingA 200
jim ThingA -200
sue ThingB 100
liz ThingC 50
I only want to see the third and fourth rows, as the values of the others match off.
I can do this if I select product, sum(value)
...
group by product
having sum(value) < 0
which works well, but I want to return the user name as well.
As soon as I add the user to the select, I need to group by it as well, which messes it up as the amounts don't match up by user AND product.
Any ideas ? I am using MS SQL 2000...
Cheers