Firstly, Hi Guys, sorry I've been away so long, I've taken on quite a large project and just haven't had the time for anything.

I am having difficulty completing my query. I initially have two tables


and Captures

So far my query creates a new table which displays a users total wallet value, what funds have already been captured from a users wallet, and what value remains in the users wallet. (In this table SearchID 1 would normally be omitted because no funds are remaining)


I'm hoping in the same query I can do the following. Let's say someone has search for £200. The query is to start from the top row (Search-Part1) and work down until the value required is matched by the sum of the selected rows. e.g.


So I'd like to be able to achieve the above. Search-Part2 has itterated down each row in Search-Part1 and added up the remaining value until the £200 has been filled. As you can see the last row of Search-Part2 is only using £25 of UserID 1005 because that is all that iss needed from the UEsers Wallet. I would be happy If I had to use (initially) all of UserID 1005 balance (£150) in the table as I'm happy requerying this at a later date to correct the value.

The final part of the Query which I can figure out simply adds Search-Part2 to the Captures Table

I hope I've managed to explain what I'm after. Its the Query to get the results from Search-Part1 to Search-Part2


Recommended Answers

All 3 Replies

Please don't worry about the formatting of the numbers as these images are just for display purposes

Hmm... I still have doubt that MySQL would be able to do that from query because the SUM() function does not select rows for you that way (it does the total of all selected). Even worse, I highly doubt it can actually cut off the value from the field (i.e. your userID 1005)... Are you sure you need to do this in query?

It's ok, I was probing for a SQL function or routine that may have provided the solution to this problem. Ok, plan B then.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.