Need help with recursive SQL

[shripaldalal]

Hi,

I am using MS Access / Jet.

Problem is of hierarchial query:

I mean typical Boss Employee table:

Name Boss
===== =====
John NULL
Sam John
Michael Sam
William Sam
Alfred William

I need a query that if I take John then all names should be returned
in a single coulmn because he is the highest boss, if i take sam, then
michael, william and alfred should be returned. If I take Michael then
only Michael should come since no one is working under him.

Ok, one boss can have many employees and no employee will have two
bosses, that's for sure. atleast in my table that's the way I have
kept it.

So I want to start on top and go to the bottom, not start from bottom
and go to the top.

Can a single SQL statement do this ? It will save a lot of trouble.

I want unlimited levels.

Best regards,
Shripal Dalal.

 

[shripaldalal]

Hi,

I am using MS Access / Jet.

Problem is of hierarchial query:

I mean typical Boss Employee table:

Name                 Boss
=====               =====
John                   NULL
Sam                   John
Michael              Sam
William               Sam
Alfred                  William

I need a query that if I take John then all names should be returned
in a single coulmn because he is the highest boss, if i take sam, then
michael, william and alfred should be returned. If I take Michael then
only Michael should come since no one is working under him.

Ok, one boss can have many employees and no employee will have two
bosses, that's for sure. atleast in my table that's the way I have
kept it.

So I want to start on top and go to the bottom, not start from bottom
and go to the top.

Can a single SQL statement do this ? It will save a lot of trouble.

I want unlimited levels.

Best regards,
Shripal Dalal.

I also cannot use sets as per another example in this news group
because updating it causes a lot of problems.

Best regards,
Shripal.

 

[Ken Snell \(MVP\)]

I believe you're going to need to fill a temporary table with the desired
records, and then use a query to show all the records that are in that
temporary table. You can fill the table by running VBA code that 'walks
down' the heirarchy, using the data that are in the temporary table as you
fill it.

 

[Michel Walsh]

MS SQL Server 2005 has proprietary syntax to deal with that kind of problem.
But you said you use Jet.

With Jet, nothing already built-in, but you can do it by yourself in,
basically, 3 ways:

- a temp table and you fill it with record by recursion from VBA code. Easy
to implement, medium execution speed. Easy to use.

- a query implying the table a finite number of time. Medium to implement,
potentially good execution speed, but limited to the fix number of 'levels'
your query handle, so medium to hard 'to use'.

- nested sets. Harder to implement than any of the other two, easy to use,
can't ask better execution speed.



So, I would try the temp table, and if execution speed is nice for you, will
keep that solution. If you know you won't have more that 4 generations, as
example, you can use a query like:

SELECT b.boss, c.boss, d.boss, e.boss, e.name
FROM ((((SELECT * FROM mytable WHERE Name ='John' ) AS a
LEFT JOIN myTable AS b ON b.boss=a.name)
LEFT JOIN myTable AS c ON c.boss=b.name)
LEFT JOIN myTable AS d ON d.boss=c.name)
LEFT JOIN myTable AS e ON e.boss=d.name



Hoping it may help,
Vanderghast, Access MVP