![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
sbornstein2
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
ASKER
can't this somehow be done in one query or a nested query? I am testing this now
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
ASKER
this is perfect though and works great, thank you.
![Avatar of chapmandew](https://filedb.experts-exchange.com/files/public/2013/11/18/2d939962-c16a-452a-9171-35ced9306a3f.png)
glad to help!
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
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
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
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
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)
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)
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
ASKER
ignore the macnum field in that above
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
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.
![Avatar of sbornstein2](https://cdn.experts-exchange.com/images/experts-exchange/avatar-01-large.gif)
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
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