1

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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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', 'mail@mail.com', '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?

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 #

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.