Assume the following scenario: we sell/invoice tickets to a show; each Tkt has a unique number.
- An invoice may contain from 1 to 15 to 20 tickets.
- A ticket can be transferred to another invoice/owner. However when transferred to another Invoice, the Tkt# changes. For expl take a look at the table:Tickets in the pic. Tkt b563 on i15652 became k622 on i15698.
The graph shows how tickets move from 1 invoice to another. For expl: i15838 has, say, 15 tickets. Some were transferred (and changed nbrs) to i16060, some to i16058, the rest stayed with original owner (on original invoice). The graph was not derived from the data in the pic, it's just to show how complicated things can get.
Easy to image that the Tickets table contains 100s of small independent mini networks/families of invoices.
Any particular family may have 1+ 'heads' (entry points) ie invoices with no parent (in the expl there are 10).
The objective is to retrieve all the invoices for any specific family. That is: if I query for any family member (i16060 for expl) how to retrieve all related invoices. ie all invoices part of the same network.
I found a few posts here but could not figure out how to solve my issue. Most solutions out there assume a single entry point, which is not my case.
Any suggestion pointer will be very much appreciated.