asked on

SQL Server - Heirarchy Question

Hello all,

I have a table called "Customer" and this table has a few fields CustID and CustParentID, I need to pull together the heirarchy of the parent and child customers based on a passed custid, if the custparentid is null then it is a top level parent for example:

CustID CustParentID
123 NULL
234 123
345 234
456 345
789 456

Now I may pass to a proc custif 345. I want a resultset the best way I can get it to show the display order I am going to show the results of the parent and childs so maybe something returned such as:

CustID RelationshipOrder
123 1
234 2
345 3
456 4
789 5

Can anyone help the best way I can do this, or a better way possibly?

Thanks

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chapmandew

here's the data I loaded:

create table #temp(custid int, custparentid int)

insert into #temp
select 123 , NULL
union all
select 234 , 123
union all
select 345 , 234
union all
select 456 , 345
union all
select 789 , 456

sbornstein2

ASKER

can't this somehow be done in one query or a nested query? I am testing this now

sbornstein2

ASKER

this is perfect though and works great, thank you.

chapmandew

glad to help!

sbornstein2

ASKER

actually this won't work only in a perfect situation. Here is what I had in my test table now.

PartyID PartyName PartyParentID
1      Company ABC      NULL
2      Company DEF      1
3      Company GHI      2
4      Company JKL      3
5      Company MNO      4
6      Jack in the Box 2      7
7      Jack in the Box 1      NULL
8      Jack in the Box 3      1
9      Jack in the Box 4      8
10      Jack in the Box 5      8

You can see now there can be more than one child flowing into a parent or another child for that matter. So here was my results when passing in 3 as the partyid:

PartyID PartyName PartyParentID Ordering
1      Company ABC      NULL      1
7      Jack in the Box 1      NULL      1
6      Jack in the Box 2      7      2
2      Company DEF      1      2
8      Jack in the Box 3      1      2
9      Jack in the Box 4      8      3
10      Jack in the Box 5      8      3
3      Company GHI      2      3
4      Company JKL      3      4
5      Company MNO      4      5

sbornstein2

ASKER

i would only want to show the flow up to the parent if I passed in partyid 3, right now this shows all the records.

with mycte(PartyID, OrgName, MacNum, PartyParentID, ordering)
as
(
select PartyID, OrgName, MacNum, PartyParentID, 1
from Organization
where PartyParentID is null
union all
select t.PartyID, t.OrgName, t.MacNum, t.PartyParentID,
ordering + 1
from Organization t
join mycte c on t.PartyParentID = c.PartyID
)

select PartyID,* from mycte o
where exists(select 1 from mycte i where PartyID = 3)

sbornstein2

ASKER

ignore the macnum field in that above

sbornstein2

ASKER

one thing is I am not sure what ordering would be right if more than one company had the same parent. I need to find that out.

sbornstein2

ASKER

i guess for now is there a way to do this and just order them based on the order of the partyid ascending I guess if there is more than one with the same parent? Just a thought