MySQL

Urgent Help with SQL Query

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Apr 2006

Posts: 1

Reputation: adambeaumont is an unknown quantity at this point

Solved Threads: 0

adambeaumont adambeaumont is offline

Offline

Newbie Poster

Urgent Help with SQL Query

Apr 16th, 2006

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

•

Join Date: Jul 2005

Posts: 483

Reputation: campkev is an unknown quantity at this point

Solved Threads: 19

campkev

Offline

Posting Pro in Training

Re: Urgent Help with SQL Query

Apr 19th, 2006

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

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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
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