About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Showing posts with label Hierarchical Data. Show all posts
Showing posts with label Hierarchical Data. Show all posts

Monday, June 10, 2013

Retrieving Hierarchical Database table using Recursive CTE



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 .

Level1      Level2      Level3
---------- ---------- ----------
mail       A          a1


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)

select * from SnehalKaQuestion
FolderId    FolderName ParentFolder
----------- ---------- ------------
1           mail       NULL
2           A          1
3           a1         2
4           B          1
5           b1         4

(5 row(s) affected)

Now we want to find the level of each folder in the folder hierarchal structure, lets see how we will be achieving this use recursive CTE.

Here we are creating a stored procedure which will use CTE to derive the required result

create procedure proc_FindFolderLevel
@folderName varchar(10)
as
begin
;with cte(folderid,foldername,parentfolder)
as
(
select folderid,foldername,parentfolder from SnehalKaQuestion where foldername = @folderName
union all
select s.folderid,s.foldername,s.parentfolder from cte c,SnehalKaQuestion s
where c.parentfolder = s.folderid)

select 'Level' + cast (ROW_NUMBER() over (order by folderid) as varchar) as Level ,foldername  from cte
end  

This stored Procedure is taking one argument as folder_name for which we need to find the hierarchy.

Let’s execute the procedure to find the result

Test 1 : With ‘a1’, to see the folder hierarchy of a1

exec proc_FindFolderLevel 'a1'

Level                               foldername
----------------------------------- ----------
Level1                              mail
Level2                              A
Level3                              a1

(3 row(s) affected)

Test 2 : With ‘b1’, to see the folder hierarchy of b1

exec proc_FindFolderLevel 'b1'

Level                               foldername
----------------------------------- ----------
Level1                              mail
Level2                              B
Level3                              b1

(3 row(s) affected)

Test 3 : With ‘A’, to see the folder hierarchy of A

exec proc_FindFolderLevel 'A'

Level                               foldername
----------------------------------- ----------
Level1                              mail
Level2                              A

(2 row(s) affected)

Test 4 : With ‘B’, to see the folder hierarchy of B

exec proc_FindFolderLevel 'B'

Level                               foldername
----------------------------------- ----------
Level1                              mail
Level2                              B

(2 row(s) affected)

Test 5: With ‘mail’, to see the folder hierarchy of mail

exec proc_FindFolderLevel 'mail'

Level                               foldername
----------------------------------- ----------
Level1                              mail

(1 row(s) affected)

Cool to witness, we can pull off this with CTE.

Heart beat is just an evidence of programmer, if your code is burning well; heart beat is just an ash...



Post Reference: Vikram Aristocratic Elfin Share

Friday, June 7, 2013

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