Hierarchical Data Storing in Database table

It was a very good morning, my friend Snehal came up with a good discussion on storing the folder structure in database, we had a good discussion and end up with a good solution.

Problem Statement:

Suppose folder structure is der like folder n subfolder so suppose “Main” folder has sub folder “A” and “B” and “A”  has “a1” and B has “b1” so how will you maintain dis data in single table and how you will get all sub folders(“A”,”B”,”a1”,”b1”) of “Main” folder .

root1      root2      root3

---------- ---------- ----------

mail       A          a1

mail       B          b1

Solution :

create table SnehalKaQuestion

(FolderId int,

FolderName varchar(10),

ParentFolder int null) 

insert into SnehalKaQuestion values(1,'mail',null),(2,'A',1),(3,'a1',2),(4,'B',1),(5,'b1',4)

delete SnehalKaQuestion

select FolderName from SnehalKaQuestion

select s1.foldername as root1, s2.foldername as root2, s3.FolderName as root3

from SnehalKaQuestion s1

left join SnehalKaQuestion s2 on s2.ParentFolder =s1.FolderId

left join SnehalKaQuestion s3 on s3.ParentFolder=s2.FolderId

where s1.FolderName = 'mail'

EXPLAINATION

S1 Table

FolderId    FolderName ParentFolder    

----------- ---------- ------------

1           mail       NULL

2           A          1

3           a1         2

4           B          1

5           b1         4

S2 Table

FolderId    FolderName ParentFolder    

----------- ---------- ------------

1           mail       NULL

2           A          1

3           a1         2

4           B          1

5           b1         4

(5 row(s) affected)

select s2.FolderId as s2FolderID,s1.foldername as root1, s2.foldername as root2

from SnehalKaQuestion s1

left join SnehalKaQuestion s2 on s2.ParentFolder =s1.FolderId

where s1.FolderName = 'mail'

S1 left Join S2

s2FolderID  root1      root2

----------- ---------- ----------

2           mail       A

4           mail       B

S3 Table

FolderId    FolderName ParentFolder    

----------- ---------- ------------

1           mail       NULL

2           A          1

3           a1         2

4           B          1

5           b1         4

select s1.foldername as root1, s2.foldername as root2,  s3.foldername as root3

from SnehalKaQuestion s1

left join SnehalKaQuestion s2 on s2.ParentFolder =s1.FolderId

left join SnehalKaQuestion s3 on s3.ParentFolder=s2.FolderId

where s1.FolderName = 'mail'

(S1 left Join S2) left join s3

root1      root2      root3

---------- ---------- ----------

mail       A          a1

mail       B          b1

Burn yourself through code, incredible will come up, we will label it new feature  

Post Reference: Vikram Aristocratic Elfin Share