mysql query parent child relation get childs

Question

I have a user table in mysql. per user has got a parentuserID in the same table. i want to crate a function that give me the all child users when i give the UserID. my table structure is below.

DROP TABLE IF EXISTS `systemuser`;
CREATE TABLE `systemuser` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `UserName` varchar(50) NOT NULL,
  `UserPassword` varchar(100) NOT NULL,
  `Email` varchar(100) DEFAULT NULL,
  `FirstName` varchar(100) DEFAULT NULL,
  `LastName` varchar(100) DEFAULT NULL,
  `UserRegistrationNo` varchar(20) DEFAULT NULL,
  `Phone` varchar(20) DEFAULT NULL,
  `ParentUserID` int(11) DEFAULT NULL,
  `VehicleLimit` int(11) DEFAULT NULL,
  `Status` bit(1) DEFAULT NULL,
  `RuleID` int(11) DEFAULT NULL,
  `IsDeleted` bit(1) NOT NULL,
  `CreatedDate` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `CreatedUserID` int(11) NOT NULL,
  `ModifiedDate` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `ModifiedUserID` int(11) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `fk_User_Rule` (`RuleID`),
  KEY `fk_User_ParentUser` (`ParentUserID`),
  CONSTRAINT `fk_User_Rule` FOREIGN KEY (`RuleID`) REFERENCES `rule` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

This is the dumy data;

-- ----------------------------
-- Records of systemuser
-- ----------------------------
INSERT INTO `systemuser` VALUES ('1', 'systemadmin', '1', '[email protected]', '20', 'Admin', '11111', '111111', null, '5', '', null, '\0', '2016-05-15 09:08:43', '1', '2016-05-15 09:08:43', null);
INSERT INTO `systemuser` VALUES ('2', 'systemadmin', '1', '[email protected]', 'System', 'Admin', '11111', '111111', '1', '5', '', null, '\0', '2016-05-15 09:09:01', '1', '2016-05-15 09:09:01', null);
INSERT INTO `systemuser` VALUES ('3', 'systemadmin', '1', '[email protected]', '3', 'Admin', '11111', '111111', '2', '5', '', null, '\0', '2016-05-15 12:29:50', '1', '2016-05-15 12:29:50', null);
INSERT INTO `systemuser` VALUES ('4', 'systemadmin', '1', '[email protected]', '4', 'Admin', '11111', '111111', '3', '5', '', null, '\0', '2016-05-15 09:09:31', '1', '2016-05-15 09:09:31', null);
INSERT INTO `systemuser` VALUES ('13', 'systemadmin', '1', '[email protected]', '5', 'Admin', '11111', '111111', '3', '5', '', null, '\0', '2016-05-15 12:29:09', '1', '2016-05-15 12:29:09', null);
INSERT INTO `systemuser` VALUES ('14', 'systemadmin', '1', '[email protected]', '6', 'Admin', '11111', '111111', '1', '5', '', null, '\0', '2016-05-15 12:30:01', '1', '2016-05-15 12:30:01', null);
INSERT INTO `systemuser` VALUES ('15', 'systemadmin', '1', '[email protected]', '7', 'Admin', '11111', '111111', '2', '5', '', null, '\0', '2016-05-15 12:29:15', '1', '2016-05-15 12:29:15', null);
INSERT INTO `systemuser` VALUES ('16', 'systemadmin', '1', '[email protected]', '8', 'Admin', '11111', '111111', '6', '5', '', null, '\0', '2016-05-15 12:30:10', '1', '2016-05-15 12:30:10', null);
INSERT INTO `systemuser` VALUES ('19', 'systemadmin', '1', '[email protected]', '9', 'Admin', '11111', '111111', '4', '5', '', null, '\0', '2016-05-15 12:29:23', '1', '2016-05-15 12:29:23', null);
INSERT INTO `systemuser` VALUES ('20', 'systemadmin', '1', '[email protected]', '10', 'Admin', '11111', '111111', '2', '5', '', null, '\0', '2016-05-15 12:30:14', '1', '2016-05-15 12:30:14', null);
INSERT INTO `systemuser` VALUES ('24', 'systemadmin', '1', '[email protected]', '11', 'Admin', '11111', '111111', '4', '5', '', null, '\0', '2016-05-15 12:30:18', '1', '2016-05-15 12:30:18', null);

in the mssql below function works good.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_RecursiveUser]
(   
    @ID INT
)
RETURNS TABLE 
AS
RETURN 
(
    WITH boss (ID,ParentUserID,Username) AS 
(SELECT ID, ParentUserID,Username FROM dbo.SystemUser 
WHERE ParentUserID = @ID ),
bossChild (ID,ParentID,Username) AS 
(
    SELECT ID, ParentUserID,Username FROM boss 
   UNION ALL
    SELECT  u.ID, u.ParentUserID,u.Username FROM dbo.SystemUser u 
    INNER JOIN bossChild b ON u.ParentUserID = b.ID 
    WHERE u.ID NOT IN (SELECT ParentID FROM boss) 
) 
SELECT * FROM  bossChild 
union 
SELECT ID, ParentUserID,Username FROM dbo.SystemUser
 WHERE ID = @ID 
)

i want to same function like this in mysql. Can anyone help me to create?

Answer 1

Yep! I finally solved my problem..

I Hope this help to somebody..

drop procedure if exists sp_RecursiveUser;

delimiter #

create procedure sp_RecursiveUser
(
in prm_UserID  int unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

create temporary table hier(
 parent_user_id smallint unsigned, 
 user_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier select ParentUserID, Id, v_depth from systemuser where Id = prm_UserID ;

create temporary table tmp engine=memory select * from hier;

while not v_done do

    if exists( select 1 from systemuser p inner join hier on p.ParentUserID = hier.user_id and hier.depth = v_depth) then

        insert into hier 
            select p.ParentUserID, p.Id, v_depth + 1 from systemuser p 
            inner join tmp on p.ParentUserID = tmp.user_id and tmp.depth = v_depth;

        set v_depth = v_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = v_depth;

    else
        set v_done = 1;
    end if;

end while;

select 
 p.Id,
 p.UserName as category_name,
 b.Id as parent_user_id,
 b.UserName as parent_category_name,
 hier.depth
from 
 hier
inner join systemuser p on hier.user_id = p.Id
left outer join systemuser b on hier.parent_user_id = b.Id
order by
 hier.depth, hier.user_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #