retrieving parent(ascendants)

i have a dimension named 'sdoc' which has 2 members
1) symptom word( first hierarchical level) and
2) doc id (second hierarchical level)

more than one 'symptom word' can have the same 'doc id'.
i want to know which all symptom words have a particular doc id (say 10.)
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.
how to retrieve all the parents.more than one 'symptom word' can have the same 'doc id'. So...there is a one-to-many relationship between 'doc id' and 'symptom word'?
if i give [10].parent then it returns only the first parent whereas it actually has more than one parent.So...there is a one-to-many relationship between 'symptom word' and 'doc id'?

Huh?

In database relationships, records normally have only one parent...

Please post the DDL code for the tables you are referencing, along with the SQL code you have tried.

Retrieving Hierarchical Data from a single table

I would like to retrieve a hierarchical list of Product Categories from a single table where the primary key is a ProductCategoryId (int) and there is an index on a ParentProductCategoryId (int) field. In other words, I have a self-referencing table. Categories at the top level of the hierarchy have a ParentProductCategoryId of zero (0). I would like to display the list in a TreeView or similar hierarchical data display control.

Is there a way to retrieve the rows in hierarchical order, sorted by CategoryName within level? I would like to do so from a stored procedure.
Example data:

ProductCategoryID CategoryDescription ParentProductcategoryID ParentCategoryDescription Level
-------------------------------------------
1 Custom Furniture 0 0
2 Boxes 0 0
3 Toys 0 0
4 Bedroom 1 Custom Furniture 1
5 Dining 1 Custom Furniture 1
6 Accessories 1 Custom Furniture 1
7 Picture Frames 6 Accessories 2
8 Serving Trays 6 Accessories 2
9 Entertainment 1 Custom Furniture 1
10 Planes 3 Toys 1
11 Trains 3 Toys 1
12 Boats 3 Toys 1
13 Automobiles 3 Toys 1
14 Jewelry 2 Boxes 1
15 Keepsake 2 Boxes 1
16 Specialty 2 Boxes 1

Desired output:

Custom Furniture
Accessories
Picture Frames
Serving Trays
Bedroom
Dining
Entertainment
Boxes
Jewelry
Keepsake
Specialty
Toys
Automobiles
Boats
Planes
Trains

Hello, if I get you right, here is a very short article that might get you started:http://www.mmkit.com/article.php?sid=345&lang=en_GB

HTH. -LV

|||The answer very much depends on whether you are using Sql Server 2005 or an older version. Assuming 2005 then have a read ofHeirarchical Queries in Sql Server 2005.|||Thanks much. This was exactly what I needed. I don't think I could have come up with this solution on my on at this point.|||

--Copy the code to run on your machine. I named your table as TreeSource with columns needed.

IF(SELECTOBJECT_ID('TreeSource','U'))ISNOTNULL

BEGIN

DROPTABLE TreeSource

END

GO

CREATETABLE [dbo].[TreeSource](

[ProductCategoryID] [int]NULL,

[CatDescription] [nvarchar](255)COLLATE SQL_Latin1_General_CP1_CI_ASNULL,

[ParentProductCategoryID] [int]NULL

)ON [PRIMARY]

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(1,'Custom Furniture',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(2,'Boxes',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(3,'Toys',NULL)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(4,'Bedroom',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(5,'Dining',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(6,'Accessories',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(7,'Picture Frames',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(8,'Serving Trays',6)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(9,'Entertainment',1)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(10,'Planes',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(11,'Trains',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(12,'Boats',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(13,'Automobiles',3)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(14,'Jewelry',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(15,'Keepsake',2)

INSERTINTO [TreeSource]([ProductCategoryID],[CatDescription],[ParentProductCategoryID])VALUES(16,'Specialty',2)

--step 0

CREATETABLE #Tree(

NodeintNOTNULLIDENTITY(100, 1),

ParentNodeint,

ProductCategoryIDintNOTNULL,

Depthtinyint,

Lineagevarchar(50))

--step 1

INSERTINTO #Tree(ProductCategoryID)SELECT ProductCategoryIDFROM TreeSourceORDERBY ParentProductCategoryID,CatDescription

Go

UPDATE TSET T.ParentNode=P.Node

FROM dbo.#Tree T

INNERJOIN TreeSource EON T.ProductCategoryID=E.ProductCategoryID

INNERJOIN TreeSource BON E.ParentProductCategoryID=B.ProductCategoryID

INNERJOIN dbo.#Tree PON B.ProductCategoryID=P.ProductCategoryID

GO

--step 3

UPDATE #TreeSET Lineage='.', Depth=0WHERE ParentNodeIsNull

--step 4

WHILEEXISTS(SELECT*FROM #TreeWHERE DepthIsNull)

UPDATE TSET T.depth= P.Depth+ 1,

T.Lineage= P.Lineage+Ltrim(Str(T.ParentNode,4,0))+'.'

FROM #TreeAS T

INNERJOIN #TreeAS PON(T.ParentNode=P.Node)

WHERE P.Depth>=0

AND P.LineageIsNotNull

AND T.DepthIsNull

--step 5 final

SELECTSpace(T.Depth*4)+ E.CatDescriptionASName

FROM TreeSource E

INNERJOIN #Tree TON E.ProductCategoryID=T.ProductCategoryID

ORDERBY T.Lineage+Ltrim(Str(T.Node,6,0))

--SELECT * FROM #Tree

DROPTable #Tree

--read more http://www.sqlteam.com/item.asp?ItemID=8866

Retrieve Data from SQL Server in an hierarchical XML.

Hi,
I have a table containing hierarchical data and I would like to retrieve it
form SQL Server as hierarchical XML so i can use it directly for binding wit
h
e.g. the ASP.NET2 Treeview
E.g table:Nodes
Table:Nodes
id parent Name
1 0 Node1
2 1 Node11
3 1 Node12
4 2 Node111
Format to be retrieved:
<nodes>
<node value="1" text="node1">
<node value="2" text="node11">
<node value="4" text="node111"></node>
</node
<node value="3" text="node12"></node>
</node>
</nodes>
- Raoul Jacobs
The nature of developping is sharing knowledge.Raoul,
First, SQLXML will not provide you with such output. You will need to take a
standard resultset and convert it to XML.
Second, if you want to retreive heirarchical data easily, then you need to
store your heirarchy properly. As you can see from your experience, the
ID/ParentID doesn't work out so well.
Search for SQL Nested Sets on google. Also, I would also recommend getting
either SQL For Smarties or Trees and Heirarchies in SQL (both by Celko).
Otherwise, your SOL and will need to develop a horribly ugly trigger or
other equally bad middle-tier code.
-- Alex Papadimoulis
"JaRa" wrote:

> Hi,
> I have a table containing hierarchical data and I would like to retrieve i
t
> form SQL Server as hierarchical XML so i can use it directly for binding w
ith
> e.g. the ASP.NET2 Treeview
> E.g table:Nodes
> Table:Nodes
> id parent Name
> 1 0 Node1
> 2 1 Node11
> 3 1 Node12
> 4 2 Node111
> Format to be retrieved:
> <nodes>
> <node value="1" text="node1">
> <node value="2" text="node11">
> <node value="4" text="node111"></node>
> </node
> <node value="3" text="node12"></node>
> </node>
> </nodes>
> --
> - Raoul Jacobs
> The nature of developping is sharing knowledge.