Hi,
I did a hierarchical table struture following this guide (
http://bit.ly/dgbjb0 ) for payments on a company. It would go like this.
A company has Payments, and each payments has subpayments, wich only the leafs are the ones registered.
A payment is considered paid only if all its subordinates are paid. So I have this data base structure:
Table: Payments
Id Int
Name Varchar
Lft Int
Rgt Int
Table: Records //There are only records of leaf payments
Id Int
Date Date
Payment Int FK
Worker Int FK
Status Int(1) //1 for paid, 0 for unpaid
Table: Workers
Id Int
Name Varchar
Now the real question is: I need a query that will return the status of all payments for all workers on X date, knowing that I only has records for leaf nodes. What's the best way of doing this? I tried with a recursive function on mysql only to find out that you can't use recursive functions on mysql :(, haven't figured out anything else