How To Get Entire Linked Group Details using SQL?

Question

In have table called "MYGROUP" in database. I display this table data in tree format in GUI as 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 is, I need to visit the lowest root for every group, if that respective group is not used in other specific tables then I would delete that record from respective table.

I need to get all the details only for the main group called "Vishal Group", please refer to both snaps, one contains entire table data and the other snap (snap which has tree format details)shows expected data i.e. I need to get only those records as a result of a SQL execution.

I tried with self join (generally we do for MGR and Employee column relationship), but no success to get the records which falls under "Vishal Group" which is the base of all records.

I have added a table DDL and Insert SQL for reference as below. And also attached a snap of how 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 which falls under "Vishal Group" ?

Teirtza

Which RDBMS is this for? Please add a tag to specify whether you're using mysql, postgresql, sql-server, oracle or db2 - or something else entirely. – Radial

Tagged to Firebird. I am using firebird database. – Teirtza

Firebird supports recursive common table expressions. You should be able to use any example for Postgres: stackoverflow.com/questions/tagged/recursive-query+postgresql – Stephanus

There is always the option of building a structure in code to represent this. Rather than trying to do something this complicated in SQL, simply create the structure you want after returning the whole table from sql. Should be very fast unless you have 100,000 rows – Exchange

Answer 1

You can use recursive stored procedure like this :

  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 input parameter is {CD1E33D1-1666-49B9-83BE-067687E4DDD6} (PK of Vishal Group

) the result is :

Update

Is it possible to get "Vishal Group" record also in the output ?

Yes, first reading you can execute procedure above in other procedure like:

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

Megilp

Hi Val, Thanks for your valuable efforts and help. While executing the code you provided I am getting an error as "Unexpected end of command - line 8, column 20". Would you please correct the code ? – Teirtza

:) .... The error is coming at line number 8 i.e. "declare variable I integer;". I am running your SP in 'FlameRobin' IDE to connect to Firebird database. – Teirtza

Ya, that helped me creating Stored Procedure in Firebird. But Val, when I executed SP in FLamerobin, as "execute procedure MYGROUP_PROC '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}' ", it is giving me only one record as an output in Flamerobin. – Teirtza

use select * from MYGROUP_PROC(..) as picture above – Megilp

Yessssssssss, VAL, YOU ARE THE MAN OF THE MOMENT. Your SQL just ROCKS, JUST ROCKS..... AWESOME KAROL, JUST AWESOME..... – Teirtza

Is it possible to get "Vishal Group" record also in the output ? – Teirtza

Answer 2

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

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}');

Octosyllabic

Answer 3

The following SQL is not written by me, but I got it from Firebird Yahoo group:

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

Teirtza

Did you tested this code? I think that the result will be only the rows which contains {CD1E33D1-1666-49B9-83BE-067687E4DDD6} in LINKED_TO_GROUP field.Sub nodes will missing. But I may be wrong. – Megilp

Yes you are right Val, sub nodes are missing. Let me see. – Teirtza

Well. Now look better. This is Recursive CTEs (Common Table Expressions)...as the answer bellow. – Megilp