Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Parent - Child-Children relaltionship
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 12/08/2004 :  08:06:31  Show Profile  Visit AskSQLTeam's Homepage
Thomas writes "Hi,

My Item table has 3 column as follows
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."

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 12/08/2004 :  09:07:06  Show Profile  Visit Seventhnight's Homepage
This link should be able to give you a hand... when it comes back up (soon )

Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000