Hi ,
Looking for an alternative in memsql of “start with connect by prior” in oracle for hierarchical queries,
From the table below we need to select name of type = ‘SITE’ (Row 4) using id =1 . Need to drill down from using id and par_id values .
CREATE TABLE Employee (
id bigint(20) DEFAULT NULL,
par_id bigint(20) DEFAULT NULL,
type varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
name varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL
/*!90618 , SHARD KEY () /
) /!90623 AUTOSTATS_CARDINALITY_MODE=PERIODIC, AUTOSTATS_HISTOGRAM_MODE=CREATE / /!90623 SQL_MODE=‘STRICT_ALL_TABLES’ */
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(1, NULL, ‘BOSS’, ‘PAVAN’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(2, 1, ‘EMP’, ‘EMP1’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(7, 6, ‘NEWEMP’, ‘EMP4’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(8, 7, ‘NEWSITE’, ‘NEWSITE’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(8, 7, ‘SITE’, ‘SITE’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(3, 2, ‘EMP’, ‘EMP2’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(4, 3, ‘SITE’, ‘SITE’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(5, NULL, ‘NEWBOSS’, ‘NEWPAVAN’);
INSERT INTO MOBILE5G.Employee
(id, par_id, type, name)
VALUES(6, 5, ‘NEWEMP’, ‘EMP3’);
select * from Employee order by id;
select * from Employee where type = ‘SITE’
and <here looking for solution using main parent ids 1 or 5>( We have inputs of main parents (id =1 or id =5)