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
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
2____________ 6________ 2_____________ 3
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