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
