How to get all related group information using SQL?

Question

The table has a MYGROUP table in the database. I display this table data in a tree format in a graphical interface as shown below:

Vishal Group | |-------Vishal Group1 | |-------Vishal Group1.1 | |-------Vishal Group1.1.1 | |-------Vishal Group2 | |-------Vishal Group2.1 | |-------Vishal Group2.1.1 | |-------Vishal Group3 | |-------Vishal Group4 | |-------Vishal Group4.1 

Actually, the requirement: I need to visit the lowest root for each group, if this corresponding group is not used in other specific tables, then I will delete this record from the corresponding table.

I need to get all the details only for the main group called "Vishal Group", please refer to both records, one contains the whole data of the table, and the other binding (a binding that has data in tree format) shows the expected data, that is, I need to get only those records as a result of SQL execution.

I tried to connect to myself (as a rule, we do it for the relationship between MGR and Employee), but I do not manage to get records that fall under the "Vishal Group", which is the basis of all the records.

I have added a DDL table and Insert SQL for reference, as shown below. And also attached a binding to how the data looks in the table.

 CREATE TABLE MYGROUP ( PK_GROUP GUID DEFAULT 'newid()' NOT NULL, DESCRIPTION Varchar(255), LINKED_TO_GROUP GUID, PRIMARY KEY (PK_GROUP) ); COMMIT; INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{11111111-111-1111-1111-111111111111} ', 'My Items', NULL); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}', 'Vishal Group', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}', 'Vishal Group1', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{A87E921D-0468-497D-92C5-19AB63751EE8}', 'Vishal Group1.1', '{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{0FDC729A-8FCC-4D23-8619-436A459835DD}', 'Vishal Group1.1.1', '{A87E921D-0468-497D-92C5-19AB63751EE8}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}', 'Vishal Group2', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}', 'Vishal Group2.1', '{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{A326E6E3-030E-493B-AA0E-DC5D90DB080F}', 'Vishal Group2.1.1', '{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{3CF1FE37-EEC0-4E79-A3C5-DB78F6A9BC05}', 'Vishal Group3', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}', 'Vishal Group4', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{2EB81764-04FA-4DDA-9AAB-A607BDC2756D}', 'Vishal Group4.1', '{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{7D939081-13F0-404C-9F2F-5222C628FDCC}', 'Sample BOMs', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{C77D2255-AC47-461D-BEE5-7F3154C23AF1}', 'Test1', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{D054539A-BBBA-4E3F-9746-1522FF8A1E89}', 'Test2', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{71B4751C-7096-4FB9-8D71-6BB19A3D9ED9}', 'Trailer Assy', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{F702BABB-73B0-4A49-B442-1C7C8A126335}', 'WIP', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{FC74D59A-94E3-4209-BCEA-1B7606EA62F1}', 'mmmmmm', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{6E4354F9-B298-4737-9C18-51B4ACAC0734}', 'test1', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{42A48EE0-D4EE-4828-BC11-D7F0D1FE5BEC}', 'test1', '{11111111-111-1111-1111-111111111111}'); INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{28AFE8E1-1221-4F94-BAE3-37EA6B360494}', 'test_2', '{11111111-111-1111-1111-111111111111}'); COMMIT; 

Any idea how to get records that fall under the "Vishal Group"?

Answer 1

You can use a recursive stored procedure as follows:

  SET TERM ^ ; create or alter procedure MYGROUP_PROC ( IPARENT varchar(64)) returns ( PK_GROUP varchar(64), DESCRIPTION varchar(255), LINKED_TO_GROUP varchar(64)) as declare variable I integer; BEGIN FOR select mygroup.pk_group, mygroup.description, mygroup.linked_to_group from mygroup where (upper(mygroup.linked_to_group) = upper(:iparent)) INTO :PK_GROUP, :DESCRIPTION, :LINKED_TO_GROUP DO BEGIN suspend; i = 0; /* Stored procedures can be nested up to 1,000 levels deep. This limitation helps to prevent infinite loops that can occur when a recursive procedure provides no absolute terminating condition.*/ while (i < 1000) do begin execute procedure mygroup_proc(:pk_group) returning_values (:pk_group,:description,:linked_to_group); if (:pk_group is null) then break; suspend; i = i+1; end END END^ SET TERM ; ^ 

When the input parameter {CD1E33D1-1666-49B9-83BE-067687E4DDD6} (PK Vishal Group

) result:

Refresh

Is it possible to get a Vishal Group record out?

Yes, the first reading, you can perform the procedure above in another procedure, for example:

 SET TERM ^ ; create or alter procedure MYGROUP_PROC_1 ( IPARENT varchar(100)) returns ( PK_GROUP varchar(64), DESCRIPTION varchar(255), LINKED_TO_GROUP varchar(64)) as BEGIN FOR select mygroup.pk_group, mygroup.description, mygroup.linked_to_group from mygroup where (upper(mygroup.description) = upper(:iparent)) /* or (upper(mygroup.pk_group) = upper(:iparent)) instead if you want to use pk_group as input parameter */ INTO :PK_GROUP, :DESCRIPTION, :LINKED_TO_GROUP DO BEGIN suspend; for select pk_group, description,linked_to_group from mygroup_proc(:pk_group) into :PK_GROUP, :DESCRIPTION, :LINKED_TO_GROUP do begin suspend; end end END^ SET TERM ; ^ 

Then use

 SELECT * FROM MYGROUP_PROC_1('Vishal Group') 

or

 SELECT * FROM MYGROUP_PROC_1('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}') 

if you use pk_group as parameter

Answer 2

As a cleaner alternative to a recursive stored procedure, a procedure using CTE (Common Table Expressions) is stored here:

 SET TERM ^ ; CREATE OR ALTER procedure RECURSIVE_MYGROUP ( PK_GROUP_IN GUID) returns ( PK_GROUP GUID, DESCRIPTION varchar(255), LINKED_TO_GROUP GUID) as begin for with recursive RECUR_MYGROUP as (select M.PK_GROUP, M.DESCRIPTION, M.LINKED_TO_GROUP from MYGROUP M where M.PK_GROUP = :PK_GROUP_IN union all select M.PK_GROUP, M.DESCRIPTION, M.LINKED_TO_GROUP from RECUR_MYGROUP RM inner join MYGROUP M on M.LINKED_TO_GROUP = RM.PK_GROUP) select * from RECUR_MYGROUP into :PK_GROUP, :DESCRIPTION, :LINKED_TO_GROUP do begin suspend; end end^ SET TERM ; ^ 

Then you can use:

 select * from recursive_mygroup('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}'); 

Answer 3

It's just for sharing knowledge, the SQL below is not written by me, but I got it from the Firebird Yahoo group. But I really respect and appreciate the help you gave me.

SQL:

 WITH RECURSIVE G1_PARENT AS ( SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1' ), R_TREE AS ( SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1 WHERE TT.LINKED_TO_GROUP IS NULL UNION ALL SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN GP.DESCRIPTION ELSE RT.PARENT END AS PARENT FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1 JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP ) SELECT * FROM R_TREE RT2 INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A 

Best wishes.

Hung