Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2008-10-13
15
Medium Priority
?
421 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 2000 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

972 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