Urgent Help with SQL Query

Expand Post »

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

Similar Threads

sql query problem with MS Access and C# in C#
C# VS 2005 - SQL Query Parameters to an ODBC DataSource in C#
sql query updating problem in Visual Basic 4 / 5 / 6
Javascript array from sql query in JSP
Please help me out with MySQL query in MySQL

adambeaumont

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

3 posts
since Apr 2006

Permalink

Apr 19th, 2006

Re: Urgent Help with SQL Query

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

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:

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

campkev

Reputation Points: 14

Solved Threads: 19

Posting Pro in Training

Offline

484 posts
since Jul 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: changing my php sql code to oracle

Next Thread in MySQL Forum Timeline: select with outer join

DaniWeb Message

Cancel Changes

User Name
Password