Find the Parent-Child Relationship along with path and Generation in SQL SERVER

In this article we are going to see the parent and child relationship with Path formation and Generation, Let we take an example in UI we have an Tree control where we have hierarchy based on that we have to save the data in DB as well as retrieve the data, for this we have to find which is root of which element and how many elements are hierarchy under the elements.

Let we take an Example along with Table, consider the System Directory and starts with sample

Create Table

CREATE TABLE DIRECTORY

(

    ID INT IDENTITY(1,1),

      NAME VARCHAR(100),

      PARENTID INT

)

Insert Records

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('d:',0)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Rajesh',1)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Sample',1)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('PDF',1)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Test',1)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('resume.doc',2)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('Documents',3)

INSERT INTO DIRECTORY(NAME,PARENTID) VALUES('c#',6)

Query 

;WITH FIR(ID,CHILD,PARENTID,GENERATION,PATH)

AS

(

SELECT ID,NAME,PARENTID,0,CAST(NAME AS VARCHAR)

FROM DIRECTORY

WHERE PARENTID = 0

UNION ALL

SELECT

REC.ID,REC.NAME,REC.PARENTID,FIR.GENERATION+1,CAST(FIR.PATH+'/'+REC.NAME AS VARCHAR)

FROM DIRECTORY AS REC

INNER JOIN FIR

ON REC.PARENTID = FIR.ID

)

SELECT CHILD,PARENTID,GENERATION,PATH

FROM FIR

ORDER BY PATH

Output:

From this article you can see how to get the parent child relationship.