Solved

How to get a list of children in a hierarquical table in SQL Server 2000?

Posted on 2008-10-13
15
398 Views
Last Modified: 2012-06-21
In SQL Server 2000.
How to get a list of children in a hierarquical table without using a stored procedure?
Is it possible?
Say I have this table and data:

[quote]
CREATE TABLE dbo.Emp
(
      EmpID            int      PRIMARY KEY,
      EmpName            varchar(30),
      MgrID            int      FOREIGN KEY REFERENCES Emp(EmpID)
)
GO


CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp(MgrID)
GO



INSERT dbo.Emp SELECT 1, 'President', NULL
INSERT dbo.Emp SELECT 2, 'Vice President', 1
INSERT dbo.Emp SELECT 3, 'CEO', 2
INSERT dbo.Emp SELECT 4, 'CTO', 2
INSERT dbo.Emp SELECT 5, 'Group Project Manager', 4
INSERT dbo.Emp SELECT 6, 'Project Manager 1', 5
INSERT dbo.Emp SELECT 7, 'Project Manager 2', 5
INSERT dbo.Emp SELECT 8, 'Team Leader 1', 6
INSERT dbo.Emp SELECT 9, 'Software Engineer 1', 8
INSERT dbo.Emp SELECT 10, 'Software Engineer 2', 8
INSERT dbo.Emp SELECT 11, 'Test Lead 1', 6
INSERT dbo.Emp SELECT 12, 'Tester 1', 11
INSERT dbo.Emp SELECT 13, 'Tester 2', 11
INSERT dbo.Emp SELECT 14, 'Team Leader 2', 7
INSERT dbo.Emp SELECT 15, 'Software Engineer 3', 14
INSERT dbo.Emp SELECT 16, 'Software Engineer 4', 14
INSERT dbo.Emp SELECT 17, 'Test Lead 2', 7
INSERT dbo.Emp SELECT 18, 'Tester 3', 17
INSERT dbo.Emp SELECT 19, 'Tester 4', 17
INSERT dbo.Emp SELECT 20, 'Tester 5', 17
[/quote]

How do I get all subordinates from Employee No 6?
(Again, without using a stored procedure)
0
Comment
Question by:fischermx
  • 6
  • 4
  • 4
  • +1
15 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22704573
You can't do it in a single select statement in SQL 2000.  If you were on SQL 2005, you could use a common table expression to do your recursion.
create table #employee_Tree

     (empid    int

     ,mgrid    int)
 

insert into #employee_tree (empid,mgrid)

select empid,mgrid from dbo.emp

where mgrid = 6

while @@Rowcount>0

     insert into #employee_tree (empid,mgrid)

     select em.empid,et.empid from dbo.emp em

       join #employee_tree et

         on em.mgrid = et.empid

     where not exists (select null from #employee_tree where empid = em.empid)
 

select * from #employee_tree

go

drop table #employee_Tree

Open in new window

0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22704608
If you are satisified with a finite number of levels:


Select 1 Level, * from dbo.Emp
where EmpID = @YourNum
union
Select 2, * from dbo.Emp
where EmpID in
   (Select EmpID from dbo.Emp where MgrID=@yourNum)
union
Select 3, * from dbo.Emp
where EmpID in
   (Select EmpID from dbo.Emp where MgrID in
       (Select EmpID from dbo.Emp where MgrID=@YourNum))
union
Select 4, * from dbo.Emp
where Empid in
   (Select EmpID from dbo.Emp where MgrId in
     (Select EmpID from dbo.Emp where MgrID in
       (Select EmpID from dbo.Emp where MgrID=@YourNum)))
...  continue as many levels as you like

 
 
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22704615

I can give u example how to do it in 1 select if are able to modify your table and add 1 more field field
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22704642
cvio123:

I'd be interested in what your approach would be even if they can't modify it.
0
 
LVL 1

Author Comment

by:fischermx
ID: 22704666
cvio123:

Yes, let's suppose I'm able to modify the table. Show us how :)
0
 
LVL 1

Author Comment

by:fischermx
ID: 22704757
Brandon:
Your approach is a lot nicer than the typical stored procedure found in BOL.
But still, it is a procedure.

dgmg I like that use of unions.


PS.
I wish I didn't keep the Joe Celko Hierarquical book so well, that I can't find it now that I need it :(


0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22705002
well here is how i did in one of my projects

What you do is made 1 mroe field varchar(50) and put full path to item into it.
When you insert new row just select your partner row, use his path and add your id +'#'

so new table emp2 and code to get results:

CREATE TABLE dbo.Emp2
(
      EmpID      int      PRIMARY KEY,
      EmpName      varchar(30),
      MgrID      int      FOREIGN KEY REFERENCES Emp2(EmpID),
                  path      varchar(50)
)
GO


CREATE NONCLUSTERED INDEX NC_NU_Emp_MgrID ON dbo.Emp2(MgrID)
GO



INSERT dbo.Emp2 SELECT 1, 'President', NULL, '1#'
INSERT dbo.Emp2 SELECT 2, 'Vice President', 1, '1#2#'
INSERT dbo.Emp2 SELECT 3, 'CEO', 2, '1#2#3#'
INSERT dbo.Emp2 SELECT 4, 'CTO', 2, '1#2#4#'
INSERT dbo.Emp2 SELECT 5, 'Group Project Manager', 4, '1#2#4#5#'
INSERT dbo.Emp2 SELECT 6, 'Project Manager 1', 5,'1#2#4#5#6#'
INSERT dbo.Emp2 SELECT 7, 'Project Manager 2', 5, '1#2#4#5#7#'
INSERT dbo.Emp2 SELECT 8, 'Team Leader 1', 6, '1#2#4#5#6#8#'
INSERT dbo.Emp2 SELECT 9, 'Software Engineer 1', 8, '1#2#4#5#6#8#9#'
INSERT dbo.Emp2 SELECT 10, 'Software Engineer 2', 8, '1#2#4#5#6#8#10#'
INSERT dbo.Emp2 SELECT 11, 'Test Lead 1', 6, '1#2#4#5#6#11#'
INSERT dbo.Emp2 SELECT 12, 'Tester 1', 11, '1#2#4#5#6#11#12#'
INSERT dbo.Emp2 SELECT 13, 'Tester 2', 11, '1#2#4#5#6#11#13#'
INSERT dbo.Emp2 SELECT 14, 'Team Leader 2', 7, '1#2#4#5#7#14#'
INSERT dbo.Emp2 SELECT 15, 'Software Engineer 3', 14, '1#2#4#5#7#14#15#'
INSERT dbo.Emp2 SELECT 16, 'Software Engineer 4', 14, '1#2#4#5#7#14#16#'
INSERT dbo.Emp2 SELECT 17, 'Test Lead 2', 7, '1#2#4#5#7#17#'
INSERT dbo.Emp2 SELECT 18, 'Tester 3', 17, '1#2#4#5#7#17#18#'
INSERT dbo.Emp2 SELECT 19, 'Tester 4', 17, '1#2#4#5#7#17#19#'
INSERT dbo.Emp2 SELECT 20, 'Tester 5', 17, '1#2#4#5#7#17#20#'

querys:


 

----------------  GET CHILDS FOR empID = 6 ------------------

Select 

 childs.*

from emp2

inner join emp2 childs

	on childs.path like emp2.path + '%'

where 

 emp2.empID = 6
 
 

----------------  GET PARENTS FOR empID = 6 ------------------

Select 

 parents.* 

from emp2

inner join emp2 parents

	on emp2.path like parents.path + '%'

where 

 emp2.empID = 6

Open in new window

0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22705863
Ok.  that's not what I was expecting.  You are talking about storing the entire hierarchy in each record.  

The big problem is if Project Manager 1 gets replaced by project manager 2, you have to recreate the path for every record that contains the old Id as opposed to just updating 6 to 66 (where 66 is the id of pm2).



Can I ask what format you intend to use this in?  I assume you want this to be part of a view which is why you want it in a select statement and not a stored procedure?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22705943
You could always us a table valued function:
create function fn_GetEmpoyeeTree (@EmpId    int)

returns @EmployeeTree TABLE (EmpId int, mgrid int)

as

begin

insert into @employeetree

select empid,@empid

from dbo.emp

where mgrid = @empid

while @@rowcount>0

insert into @employeeTree

select em.empid,et.empid from dbo.emp em

join @employeetree et

  on em.mgrid = et.empid

  where not exists (select null from @employeetree where empid = em.empid)

return

end

go

select *from dbo.fn_GetEmpoyeeTree(6)

Open in new window

0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22705952
@Brandon: i dont see problem storing entire hierarhy in each record there shouldnt be more than 40 char and thats if u have like 10+ nodes in deep

about updating its even simplier its just 1 update where u replace records that have old path with new path and it will replace all childs as well so i dont see big deal about that eather

This way to get child works much faster than using function or while statment in SP dont u agree ?
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22706082
Yes it is faster, but is the overhead of maintenance worth the change in select?  That is the for the asker to decide.  10000 back to back executions of each resulted in 17.2 (function) vs 8.3 seconds (select with new column).
0
 
LVL 5

Expert Comment

by:Cvijo123
ID: 22706259

@Brandon: strange thing is you always trying to outsmart anyone who post here and probably when u see not even master rank then its even worse. Its not first time u did that to me.

For this example in my project i used function and at some point with many joins, soulution with path worked much much much faster.

And overhead of mainteance ? pffft tell me you are joking, what overhed. First tree hierarchy doesnt change often and second adding 1 more update in your SP that manage that table is overhead ?

whatever, ill just try to pass question u are posting too so we dont see those outsmarting posts.

fischermx sry for ruing your question with this but Brandon just pushing it so it breaked here, sry again and thats all from me.
0
 
LVL 1

Author Comment

by:fischermx
ID: 22706385
I think I will use the table valued function. I barely remember that option was available :( (returning a table from a function).

Though, the correct answer to my original question is the multi-union statement.
0
 
LVL 1

Author Comment

by:fischermx
ID: 22706430
Cvijo123:

I knew your solution already, from that Joe Celko book. It has its pros and cons.
I've used it in a software where an almost static hierarchical classification was used. And yes, it is the faster option for readings, though maintenance _is_ painful as Brandon pointed.

But this time, the hierarchy is highly dynamic, and I couldn't use that.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22706563
"@Brandon: strange thing is you always trying to outsmart anyone who post here and probably when u see not even master rank then its even worse"

I don't care about what someone is ranked.  Everyone starts somewhere.  To reference a comment I read somewhere, rank and points are a measure of participation not of competence or skill.  I believe I read that angelIII made that statement and it is SO true.  And I don't go into things trying to outsmart anyone.  Far from it.  I just try to give my opinion on approaches listed if I have one and provide different options.  Did I say there was anything wrong with what you provided, no.  But one method can be easily implemented with no data model changes and the other requires a data model change and an initial population of the new column.  

"adding 1 more update in your SP that manage that table is overhead"  
I think we can all agree that in an ideal world, all database operations (read/write of data) would be handled by stored procedures.  But unfortunately we don't live in that ideal world.  Look around EE, a fair portion of the questions asked show clear examples of embedded SQL in application code.    

Most organizations have some sort of change management process.  Granted, such an organization would probably not be doing a lot of embedded SQL, but it does happen.

99% of what I post is "That is the for the asker to decide".  While I don't always say it, I am never offended by someone not choosing my solution over another.  People post different ways of achieving the same result ALL the time.  There are ups and downs to each method and it is up to the person seeking help to decide what is best for them.

If you care to discuss this further, a quick google search of my name will put you in contact with me.  I'm not out to offend anyone here, I am a volunteer just as you are.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now