SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
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  

AskSQLTeam
Ask SQLTeam Question

USA
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
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."

Seventhnight
Flowing Fount of Yak Knowledge

USA
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 )

http://www.seventhnight.com/treestructs.asp

Corey
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.06 seconds. Powered By: Snitz Forums 2000