I want to expand/explode an employee table to show all the employees a
boss is responsible for (including him/herself). Then I can say SELECT
Emp FROM Employee WHERE Boss = 'Jane'. It should return Jane, Jill,
and Sally.
Or if anyone knows how to convert this to a nested set hierarchy then
that would be helpful too. I figure exploding the table would be
easier for a newbie like me. Apparently it's not that easy. Thanks...

Explode this table:

Emp Boss
John John
Tom John
Jane John
Jill Jane
Ed Tom
Sally Jill

To this table:

Emp Boss
John John
Tom John
Jane John
Jill John
Ed John
Sally John
Jane Jane
Jill Jane
Sally Jane
Tom Tom
Ed Tom
Sally Jill
Sally Sally

I have this so far. Not sure if it's the right approach. Doesn't
work. It just loops forever:

CREATE PROCEDURE expand_table()
BEGIN
DECLARE sEmp VARCHAR(10);
DECLARE sBoss VARCHAR(10);
DECLARE done INT DEFAULT 0;
DECLARE csrEmp CURSOR FOR SELECT Emp FROM Employee;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

TRUNCATE TABLE ExpEmp;
OPEN csrEmp;
REPEAT
FETCH csrEmp INTO sEmp;
IF NOT done THEN
SET sBoss = (SELECT Boss FROM Employee WHERE Emp = sEmp);
WHILE sEmp != sBoss DO
INSERT INTO ExpEmp VALUES (sEmp, sBoss);
SET sBoss = (SELECT Boss FROM Employee WHERE Emp = sBoss);
END WHILE;
END IF;
UNTIL done END REPEAT;

CLOSE csrCntr;

END$$

EMAIL REMOVED wrote:
> I want to expand/explode an employee table to show all the employees a
> boss is responsible for (including him/herself). Then I can say SELECT
> Emp FROM Employee WHERE Boss = 'Jane'. It should return Jane, Jill,
> and Sally.
> Or if anyone knows how to convert this to a nested set hierarchy then
> that would be helpful too. I figure exploding the table would be
> easier for a newbie like me. Apparently it's not that easy. Thanks...
>
> Explode this table:
>
> Emp Boss
> John John
> Tom John
> Jane John
> Jill Jane
> Ed Tom
> Sally Jill
>
> To this table:
>
> Emp Boss
> John John
> Tom John
> Jane John
> Jill John
> Ed John
> Sally John
> Jane Jane
> Jill Jane
> Sally Jane
> Tom Tom
> Ed Tom
> Sally Jill
> Sally Sally
>
> I have this so far. Not sure if it's the right approach. Doesn't
> work. It just loops forever:
>
> CREATE PROCEDURE expand_table()
> BEGIN
> DECLARE sEmp VARCHAR(10);
> DECLARE sBoss VARCHAR(10);
> DECLARE done INT DEFAULT 0;
> DECLARE csrEmp CURSOR FOR SELECT Emp FROM Employee;
> DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
>
> TRUNCATE TABLE ExpEmp;
> OPEN csrEmp;
> REPEAT
> FETCH csrEmp INTO sEmp;
> IF NOT done THEN
> SET sBoss = (SELECT Boss FROM Employee WHERE Emp = sEmp);
> WHILE sEmp != sBoss DO
> INSERT INTO ExpEmp VALUES (sEmp, sBoss);
> SET sBoss = (SELECT Boss FROM Employee WHERE Emp = sBoss);
> END WHILE;
> END IF;
> UNTIL done END REPEAT;
>
> CLOSE csrCntr;
>
> END$$
>

Your problem is you have Jane as her own boss. This is what's causing
your loop.

If Jane is at the top, than she should have no boss (null value). If
she's not, she should have a different boss.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
EMAIL REMOVED
==================