Hi All,

   Following is the secnario,                 

I have two tables one is dimension table and other is hierarchy table, which contains the following type of data

Dimension Table

MemberID Label

NULL       NONE

1              A

2              B

3              C

4              D

5              E

6              F

Hierarchy Table contains the following

MemberID ParentMemberID

1                  NULL

2                    1

3                    1

4                    2

5                    4

6                   5

Now by joining the above two tables i want to populate the other table which contains the

following columns,

Label ParentLabel

The data for the above table should look like following

Label ParentLabel

A         NULL

B          A

C          A

D          B

E          D

F          E

Please help me in achieving the above scenario

Here is one way:

SELECT

  d.Label,

  d1.Label AS ParentLabel

FROM DimensionTable d

LEFT OUTER JOIN HierarchyTable h ON d.MemberId = h.MemberId

LEFT OUTER JOIN DimensionTable d1 ON d1.MemberId = h.ParentMemberId