Ask SQLTeam Question
Posted - 12/08/2004 : 08:06:31
| Thomas writes "Hi,
My Item table has 3 column as follows
In my Item table, I have few records as follows
In this Item table, ParentItem column stores the Parent Item ID which is a record from Item table itself.
One Item can have only one parent, but the same parent Item can be a parent of another Item.
(sample records: Item ID 55 is the Parent Item of Item 50 and Item 60.)
Child Item can be a Parent of another Item but Circular Reference is not allowed.
(sample records: Item ID 50 is the Parent Item of Item 59,
but Item 50 can't be a Parent Item of Item 55 because Item 55 is the Parent Item of Item 50)
This Parent - Child - Children realtionship can go up any level.
I am not sure how to call the relationship between these records, i just call it Parent - Child - Children.
My quesion is now I want a list of all the possible related item list. based on my sample records,
lets say if i want all the related items for Item 50, that will be
[Item 50 is the item which i requested, Item 55 is the parent of Item 50,
Item 59 is the child of Item 50, Item 60 is the child of Item 55(Item 55 is parent of Item 50)]
how i will write the sql query for this? please give me hand on this query."