Hierarchical Data usign Self Join

Gagan Gautam

10y917SQL

this is my table with ParentId column. now i want make parent and child in this given table using self join.

I searched on internet but these is not good solution.I am using this table in Entity Framwork

My Table

___________________________

ID Title ParentId

31	Retial	NULL
32	Mortgogages	31
34	UK Mortgages	32
35	Buy To Let	32
36	Credit Card	31
37	Corporate	NULL

Output

e.g

31 has 0 child

32 has 2 child

32 has 0 child

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke10y ago
We got to be very careful about choose terms and words as they have very specific meaning.
May be you wanted to say in your original post that 31 has 0 parent (and not the child). Because that's what the diagram indicates.
32 has two children is fine, but my question was, why was it written twice in your original post as
32 has 2 child
32 has 0 child
If you was numbers like this, I guess solution given by Vishal Jadav (group by) is sufficient. You don't really have to worry about hierarchical data here.
Vishal Jadav10y ago
Hi,
But you can then get the count using group by.
At least it will give the total child under a parent right ?
Then just use the group by mainid and get the count .
Please try.
Thanks.
Gagan Gautam10y ago
HiNitin Sontakke
I think it should be Understandable
Gagan Gautam10y ago
HIVishal Jadav
this query create parent and child node
Nitin Sontakke10y ago
Out is not quite clear to me, sorry!
How come you are saying 31 has 0 children as 32 is the child of 31. And how you are showing one entry as "32 has 2 child" and again "32 has 0 child"? Please explain further.
Vishal Jadav10y ago
Hi,
Try below query. Its the one i am using right now.
I have same table structure as you.
Please make change fields as per you need.
-------------------------------------------------
CREATE FUNCTION [dbo].[GETDOWNLINE](@MAINID BIGINT, @UP INT)
RETURNS @USERS TABLE(MID BIGINT NOT NULL)
BEGIN
IF(@UP = 1) --RETRIVE DOWNLINE
BEGIN

WITH ancestors AS (
SELECT mainid,upperid FROM USERS WHERE mainid=@MAINID
UNION ALL
SELECT p.mainid,p.upperid FROM USERS p INNER JOIN ancestors a ON a.mainid=p.upperid)
INSERT INTO @USERS SELECT mainid FROM ancestors
END
ELSE IF(@UP =0) --RETRIVE UPLINE
BEGIN
WITH ancestors AS (
SELECT mainid,upperid FROM USERS WHERE mainid=@MAINID
UNION ALL
SELECT p.mainid,p.upperid FROM USERS p INNER JOIN ancestors a ON a.UPPERID = p.MAINID )
INSERT INTO @USERS SELECT mainid FROM ancestors
END
RETURN
END
-------------------------------------------------
Hope it helps.
Thanks.

Hierarchical Data usign Self Join

HiNitin Sontakke

HIVishal Jadav