I have a table named "Nodes" with the following columns: "companyID", "parentID"
In this table a "Parent/Child" relationship is kept, in order to know which companies belong to who.
There can be many levels while creating the compnay structure based on the data of the table.
Think of the following scenario:
The top level company id = 1 (has no parent)
1 has 3 children with the following IDs: 10, 50, 80
50 has 2 children with the following IDs: 200, 400
200 has 1 child with the following ID: 5000
So, what i need is an efficient query to return as fast as possible, all the parents and children (whole structure) of a given company ID.
For example, given the ID:200 query should return the following results (two columns: companyID AND its parentID):
column A column B