Urgent Help with SQL Query
 

Good Evening,

I currently have an issue regarding a query I wish to perform against a particular table in my database, any help would be much apprieciated

Background:

I am designing a website service which operates with the use of credits, if they pay a fee they are given a number of credits, when they use the service these credits decrease..
i have implemented a transaction table which aims to store all the transactions of credits purchased and used.
ie. if a user purchases 2 credits the table transaction will be updated and the value of 1 will be inserted.

if a user uses their credits the table transaction will be updated and the value of 0 will be inserted, this allows for differentiation i.e. entries with a value of 0 mean a user has used their credits rather than purchase..


Table : Transactions

TransactionID , Credits , BuyerID, Value

1_____________2_________1______________1
2____________ 6________ 2_____________ 3
3____________ 4_________1______________2
4____________ 6_________1______________3
5____________2_________2_______________0


The BuyerID field is the particular ID of the user
Credits - is the number of credits being used in the transaction
Value - value of the transaction


Aim: implement a query which selects the number of credits a user has remaining i.e. credits purchased - credits used (value 0)

Example- with the above table - the user with the BuyerID 2 table would have purchased 6 credits in TransactionID 2 and used 2 credits in TransactionID - remaining credits = 4

I hope I havn't confused anyone


Thanks

Adam

Re: Urgent Help with SQL Query
 

I don't use mysql but both of these work in ms-sql:
query 1:

declare @buyerid int

set @buyerid = 2

declare @creds int

declare @debs int



set @creds =(select sum(credits) from Transactions where buyerid = @buyerid and value >0)

set @debs =(select sum(credits) from Transactions where buyerid = @buyerid and value =0)

select (@creds - @debs) as Balance

query 2:

declare @buyerid int

set @buyerid = 2

select (Sum(t1.credits) - Sum(t2.credits)) as Balance

from transactions t1, transactions t2 where

t2.buyerid = @buyerid and t1.buyerid = @buyerid and t1.value > 0 and t2.value = 0