Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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

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
fischermx
Asked:
fischermx
  • 6
  • 4
  • 4
  • +1
1 Solution
 
BrandonGalderisiCommented:
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
 
dqmqCommented:
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
 
Cvijo123Commented:

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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
BrandonGalderisiCommented:
cvio123:

I'd be interested in what your approach would be even if they can't modify it.
0
 
fischermxAuthor Commented:
cvio123:

Yes, let's suppose I'm able to modify the table. Show us how :)
0
 
fischermxAuthor Commented:
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
 
Cvijo123Commented:
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
 
BrandonGalderisiCommented:
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
 
BrandonGalderisiCommented:
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
 
Cvijo123Commented:
@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
 
BrandonGalderisiCommented:
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
 
Cvijo123Commented:

@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
 
fischermxAuthor Commented:
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
 
fischermxAuthor Commented:
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
 
BrandonGalderisiCommented:
"@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

Independent Software Vendors: 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!

  • 6
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now