I would like to write a query to return nested data. The data has a master, child relationshiop and teh child is linked to the master record through the 3 column (LinkedKey). The reason this is all in one table is that the parent and child records all have the same data elements so there is not really a need to have a second table seperating the child from the parent. An example is that I have a single table called Items. The table has 3 columns, Key, Description, and LinkedKey. Data example is as follows
1 First Description null
2 Second Description 1
3 Third Description 1
4 Fourth Description null
5 Fifth Description 1
6 Sixth Description 4
What I want to achieve is populating a grid where the parent is displayed first then all of its children then the next parent and its children, etc. So from the above data i would like to return a dataset that has the data in teh following order
1 First Description null
2 Second Description 1
3 Third Description 1
5 Fifth Description 1
4 Fourth Description null
6 Sixth Description 4
Not sure if this is possible in one query or if I will need to peform the first query to select all records that have a null LinkedKey and then loop through that result set and retrieve the all records that have a linked key equal to the Key of the item being processed.
Thanks for your help in advance !!!
1 First Description null
2 Second Description 1
3 Third Description 1
4 Fourth Description null
5 Fifth Description 1
6 Sixth Description 4
What I want to achieve is populating a grid where the parent is displayed first then all of its children then the next parent and its children, etc. So from the above data i would like to return a dataset that has the data in teh following order
1 First Description null
2 Second Description 1
3 Third Description 1
5 Fifth Description 1
4 Fourth Description null
6 Sixth Description 4
Not sure if this is possible in one query or if I will need to peform the first query to select all records that have a null LinkedKey and then loop through that result set and retrieve the all records that have a linked key equal to the Key of the item being processed.
Thanks for your help in advance !!!