If you want to learn about recursivity with Firebird and you can read Spanish, there are several articles that can teach you:
https://firebird21.wordpress.com/2015/08/22/usando-recursividad-con-cte/ https://firebird21.wordpress.com/2015/08/25/entendiendo-la-recursividad-en-los-select/ https://firebird21.wordpress.com/2013/10/10/stored-procedures-recursivos/ https://firebird21.wordpress.com/2015/08/27/ejemplo-de-recursion-1/ https://firebird21.wordpress.com/2015/08/28/ejemplo-de-recursion-2/ https://firebird21.wordpress.com/2015/08/29/ejemplo-de-recursion-3/ https://firebird21.wordpress.com/2015/08/30/ejemplo-de-recursion-4-actualizando-filas-recursivamente/ https://firebird21.wordpress.com/2015/08/31/ejemplo-de-recursion-5-saldos-acumulados/ https://firebird21.wordpress.com/2015/09/05/ejemplo-de-recursion-6-repitiendo-las-filas/ Greetings. Walter. On Fri, Oct 2, 2015 at 7:52 AM, Vishal Tiwari vishuals...@yahoo.co.in [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 > liviusliv...@poczta.onet.pl [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 > vishuals...@yahoo.co.in [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_GROUP**DESCRIPTION**LINKED_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_GROUP**DESCRIPTION**LINKED_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 > vishuals...@yahoo.co.in [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] > > > > > > > >
