Member Avatar for J.C. SolvoTerra

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

UserWallet
b4eb49cf9784417d61d6a5d56c089da9

and Captures
40061e6629b7d4577f1cf2405e54ab01

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)

Search-Part1
a43481228bcb75f2be9cd00a1e7f12ff

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.

Search-Part2
a2912922c2b5cfe718d8fde131b7054f

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

Thanks

Search4.png 3.58 KB
Edited by J.C. SolvoTerra
  • 2 Contributors
  • 3 Replies
  • 195 Views
  • 5 Hours Discussion Span
  • Latest Post Latest Post by J.C. SolvoTerra
Member Avatar for J.C. SolvoTerra

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

Member Avatar for Taywin

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?

Member Avatar for J.C. SolvoTerra

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.