stored procedure join multiple result sets

[chathura86]

Is there a way to join few results sets in to a one result set? I have a table with recursive relation.

eg. User table has a column with boss with the relation to the same
User table

+-------+---------------+-------+
| id	| name		| boss	|
+-------+---------------+-------+
| 1	| User 1	| NULL	|
| 2	| User 2	| 1	|
| 3	| User 3	| 2	|
| 4	| User 4	| 3	|
| 5	| User 5	| 4	|
| 6	| User 6	| 5	|
| 7	| User 7	| 6	|
| 8	| User 8	| 7	|
| 9	| User 9	| 8	|
+-------+---------------+-------+

DELIMITER //
DROP PROCEDURE IF EXISTS getUserList//
CREATE PROCEDURE getUserList(IN UserId int(32))
	BEGIN
		DECLARE done INT DEFAULT 0;
		DECLARE intUser, intBoss INT;
		DECLARE cur_users CURSOR FOR SELECT `id`, `boss` FROM User WHERE `id` = UserId;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET  done = 1;

		OPEN cur_users;

		REPEAT
			FETCH cur_users INTO intUser, intBoss;
			IF intBoss != NULL THEN
				SELECT intUser as `User`;
				CALL getUserList(intBoss);
			END IF;
		UNTIL done END REPEAT;

		CLOSE cur_users;

	END //
DELIMITER ;

When you pass the user id to the stored procedure it will find the relevant boss for that user and then again using boss as the user id finds his boss and again his boss and so on till it reaches the top so I can get all user ids of the bosses. now what I need is a way to union these results to a single table

So I will get the user list as a table. Example:

If I call:

getUserList(5);

the result should be like

+-------+
| User| 
+-------+
| 1	| 
| 2	| 
| 3	| 
| 4	| 
| 5	| 
+-------+

This returns all the bosses above him.

Regards
Chathura Bamunusinghe.

[MMcCarthy]

I think this will give you what you want but mysql is not my first language so please excuse any syntax errors :)

It should set you on the right road at least.

CREATE PROCEDURE getUserList(IN UserId int(32)) 

BEGIN
    CREATE temporary table User
    (Uid int
    );

    BEGIN 
        DECLARE done INT DEFAULT 0; 
        DECLARE intUser, intBoss INT; 
        DECLARE cur_users CURSOR FOR SELECT `id`, `boss` FROM User WHERE `id` = UserId; 
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET  done = 1; 
  
        OPEN cur_users; 

        REPEAT
         
        FETCH cur_users INTO intUser, intBoss; 
            IF intBoss != NULL THEN 
                Insert INTO User (Uid) Values (intUser);
                CALL getUserList(intBoss); 
            END IF; 
        UNTIL done END REPEAT;
  
        CLOSE cur_users; 
  
    END 

END// 
DELIMITER ;