|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 12/08/2004 : 08:06:31
|
Thomas writes "Hi,
My Item table has 3 column as follows
ID INT
Description NVARCHAR(150)
ParentItem INT
In my Item table, I have few records as follows
50,Item A,55
55,Item AA,0
56,Item B,57
57,Item BB,0
58,Item C,56
59,Item D,50
60,Item AAA,55
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
50,Item A
55,Item AA
59,Item D
60,Item AAA
[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." |
|
Parent - Child-Children relaltionship
Forum Locked
)