0

My database structure like ..

user table

| userid     | parentid         |
| -------- --| ---------------- |
| 343932     | 112333           |
| 386383     | 343932           |
| 673443     | 386383           |
| 398933     | 673443           |
| 687330     | 673443           |

user Balance table

| user       | balance          |
| -------- --| ---------------- |
| 112333     | 399              |
| 343932     | 900              |
| 386383     | 800              |
| 673443     | 9000             |
| 398933     | 6000             |
| 687330     | 499              |

condition is if parent has balance user can do transaction. for example user(398933) wants to do transaction of $100 but it parent(673443) has balance but user(673443)'s parent 386383 has no sufficient balance. if user ultimate parent has sufficient balance .. transaction will be succeeded. is there any way to join technicities to get every parents balance in a single queries so program can decide when to discard request.

if any PHP technique, pls advise

Michel
  • 4,076
  • 4
  • 34
  • 52
AduNet
  • 1
  • So you go from user `398933` to parent `673443` to user `673443` to parent `386383` to user `386383` to ... where does it end? Looks like you have to rethink your table structure. And to answer your question: the only way to do it is to retrieve everything from the two tables, get user->parent pair, get new user->parent pair, get new user->parent pair etc.. all the time looking at the balance. – Michel Oct 16 '21 at 06:02
  • 1
    Please provide the exact result you expect, using the given data. If I have a balance of $100 and my parent has a balance or $50: 1) first, is that possible? 2) Why would I not be able to support a transaction of $70 when I have $100? 3) Why would my parent balance impact my ability? 4) Can a balance be negative? Explain your logic completely? Maybe your test case is not valid and parent balances are sums of all children balances. Show a complete test case with expected results. – Jon Armstrong Oct 16 '21 at 07:34
  • if the depth of your tree is limited you can recurse it manually. otherwise depending on the database itself you can dig in hierarchical queries a good start there :https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Tuckbros Oct 16 '21 at 10:15

0 Answers0