Hierarchical Data usign Self Join

Question

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

Nitin Sontakke · Answer

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 Jadav · Answer

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 Gautam · Answer

Hi Nitin Sontakke I think it should be Understandable

Gagan Gautam · Answer

HI Vishal Jadav this query create parent and child node

Nitin Sontakke · Answer

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 Jadav · Answer

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (6)