Urgent Help with SQL Query

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 360,995 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,457 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser:

Thread Tools Display Modes

Views: 1417 | Replies: 1

•

Join Date: Apr 2006

Posts: 1

Reputation: adambeaumont is an unknown quantity at this point

Rep Power: 0

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

Location: Dallas, TX

Posts: 481

Reputation: campkev is an unknown quantity at this point

Rep Power: 3

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

(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

query 2:

Help with Code Tags

(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