Subject Re: Re: [firebird-support] How To Get Entire Linked Group Details
Author Walter R. Ojeda Valiente
Post date 2015-10-02T12:24:44Z
If you want to learn about recursivity with Firebird and you can read Spanish, there are several articles that can teach you:










Greetings.

Walter.


On Fri, Oct 2, 2015 at 7:52 AM, Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> wrote:
 

Hi Karol Bieniaszewski,


Yessssssssss, YOU ARE THE MAN OF THE MOMENT. 

Your SQL just ROCKS, JUST ROCKS..... AWESOME KAROL, JUST AWESOME.....


I am going to next level of my code where I need to find out that the deepest leaf, if it is not used in certain table then I would like to delete it.

For time being one more questions (Please expect more in upcoming time on this issue :) ), is it possible to get the records for one entire leaf and it dependent leafs and then another leaf and its dependent and so on via your SQL ?


Result order like:

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


With Best Regards.

Vishal




On Friday, 2 October 2015 4:59 PM, "liviuslivius liviuslivius@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
Hi,
 
i do not know if you describe your problem precisely.
But i understand it like this:
looks like you need all leaf from same parent (with parent included) as is for "Vishal Group1" and all its childs
 
try this
 
 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 
 
with this query i got the same result as you showed in table below 
 
regards,
Karol Bieniaszewski
 
 
W dniu 2015-10-02 06:36:16 użytkownik Vishal Tiwari vishualsoft@... [firebird-support] <firebird-support@yahoogroups.com> napisał:
 
Hi All,
 
There is some change in my previous SQL. Please consider below SQLs.
 
CREATE TABLE MYGROUP 
(
  PK_GROUP GUID DEFAULT 'newid()' NOT NULL,
  DESCRIPTION Varchar(255),
  LINKED_TO_GROUP GUID,
  PRIMARY KEY (PK_GROUP)
);
 
COMMIT;
 
INSERT INTO MYGROU P (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}');
IN SERT 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-C3F6C63F859 1}');
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, LI NKED_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', '{111 11111-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;
 
------------------------------------------------------------------------------------------------------------------
 
After executing above SQLs, you would see below table data.
 
PK_GROUPDESCRIPTIONLINKED_TO_GROUP
{11111111-111-1111-1111-111111111111} My Items[null]
{CD1E33D1-1666-49B9-83BE-067687E4DDD6}Vishal Group{11111111-111-1111-1111-111111111111} 
{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}Vishal Group1{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{A87E921D-0468-497D-92C5-19AB63751EE8}Vishal Group1.1{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}
{0FDC729A-8FCC-4D23-8619-436A459835DD}Vishal Group1.1.1{A87E921D-0468-497D-92C5-19AB63751EE8}
{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}Vishal Group2{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}Vishal Group2.1{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}
{A326E6E3-030E-493B-AA0E-DC5D90DB080F}Vishal Group2.1.1{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}
{3CF1FE37-EEC0-4E79-A3C5-DB78F6A9BC05}Vishal Group3{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}Vishal Group4{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{2EB81764-04FA-4DDA-9AAB-A607BDC2756D}Vishal Group4.1{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}
{7D939081-13F0-404C-9F2F-5222C628FDCC}Sample BOMs{11111111-111-1111-1111-111111111111} 
{C77D2255-AC47-461D-BEE5-7F3154C23AF1}Test1{11111111-111-1111-1111-111111111111} 
{D054539A-BBBA-4E3F-9746-1522FF8A1E89}Test2{11111111-111-1111-1111-111111111111} 
{71B4751C-7096-4FB9-8D71-6BB19A3D9ED9}Trailer Assy{11111111-111-1111-1111-111111111111} 
{F702BABB-73B0-4A49-B442-1C7C8A126335}WIP{11111111-111-1111-1111-111111111111} 
{FC74D59A-94E3-4209-BCEA-1B7606EA62F1}mmmmmm{11111111-111-1111-1111-111111111111} 
{6E4354F9-B298-4737-9C18-51B4ACAC0734}test1{11111111-111-1111-1111-111111111111} 
{42A48EE0-D4EE-4828-BC11-D7F0D1FE5BEC}test1{11111111-111-1111-1111-111111111111} 
{28AFE8E1-1221-4F94-BAE3-37EA6B360494}test_2{11111111-111-1111-1111-111111111111} 
 
I need to get below data only, when I provide "Vishal Group1" value in 'Description' column, as a condition in SQL.
 
PK_GROUPDESCRIPTIONLINKED_TO_GROUP
{CD1E33D1-1666-49B9-83BE-067687E4DDD6}Vishal Group{11111111-111-1111-1111-111111111111} 
{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}Vishal Group1{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{A87E921D-0468-497D-92C5-19AB63751EE8}Vishal Group1.1{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}
{0FDC729A-8FCC-4D23-8619-436A459835DD}Vishal Group1.1.1{A87E921D-0468-497D-92C5-19AB63751EE8}
{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}Vishal Group2{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}Vishal Group2.1{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}
{A326E6E3-030E-493B-AA0E-DC5D90DB080F}Vishal Group2.1.1{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}
{3CF1FE37-EEC0-4E79-A3C5-DB78F6A9BC05}Vishal Group3{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}Vishal Group4{CD1E33D1-1666-49B9-83BE-067687E4DDD6}
{2EB81764-04FA-4DDA-9AAB-A607BDC2756D}Vishal Group4.1{1EC302C8-0AB3-4F67-B47A-CC43401DF4ED}
 
In tree format, the logical structure looks like 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
 
 
I tried self join but couldn't get above result when I specify the above mentioned condition.
 
Actually, the requirement is, I need to visit the lowest root for every group (groups which falls under 'Vishal Group1' because he is the base group.) if that group is not used in specific table then I would delete that record from respective table.
 
 
Please help. 
 
Thanks In Advance.
 
 
With Best Regards.
 
Vishal
 
 
 
 
 



On Thursday, 1 October 2015 6:29 PM, "Vishal Tiwari vishualsoft@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:


 
Hi All, 
I have attached a table DDL and Insert  record script in "Table Script.txt" text file. And also attached a snap of how data looks in the table in the file "Entire Table Data.png"
I need to get all the details for the main group called "Vishal Group1", please refer "Expected Result.png". In "Expected Result.png" I have shown a tree structure and the expected data as a result of SQL. 
I tried with self join (generally we do for MGR and Employee columns), but so success.
How do I get the result only for all groups which comes under "Vishal Group1". please refer "Expected Result.png". 
Thanks in advance.
With Best Regards.
Vishal
----------

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 Group1', Null);

INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}', 'Vishal Group1.1', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{A87E921D-0468-497D-92C5-19AB63751EE8}', 'Vishal Group1.1.1', '{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}');

INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}', 'Vishal Group2.1', '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES ('{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}', 'Vishal Group2.1.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.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;


[Non-text portions of this message have been removed]