Subject RE: Re: [firebird-support] How To Get Entire Linked Group Details
Author Norbert Saint Georges
Post date 2015-10-02T13:26:57Z

Without CTE

 

select a.description from mygroup a

where a.linked_to_group is not null

and

(

exists(select 1 from mygroup b  where  b.linked_to_group = a.pk_group )

or

a.linked_to_group != (select first 1 pk_group from mygroup where linked_to_group is null)

)

 

 

   Norbert Saint Georges

 

   TetraSys Oy

   Bergantie 69, FI-02540 Kylmälä

   Tel. : +358 (0) 400 27 25 18

   E- mail : nsg@...

 

De : firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Envoyé : vendredi 2 octobre 2015 15:24
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details

 

 

 

Hi Karol Bieniaszewski,

 

You are passing "Vishal Group1" in condition, I need to pass "Vishal Group" (i.e. base leaf for perticular group), because in the sql you provided if I put 'Test1', it gives entire structure details, one way that is correct, but only i need is to pass required base group as in the condition and not any sub-leaf in condition, could you please modify it ?

 

Thanks Again In Advance.

 

With Best Regards.

 

Vishal

 

 

On Friday, 2 October 2015 5:23 PM, "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_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

(Message over 64 KB, truncated)