Subject RE: Re: [firebird-support] How To Get Entire Linked Group Details
Author Norbert Saint Georges
Post date 2015-10-05T10:25:28Z

if I understand the request, something like

 

select b.description from

(

select a.* 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)

)

) b

where b.linked_to_group = (select first 1 pk_group from mygroup where description = 'Vishal Group')

 

 

   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é : lundi 5 octobre 2015 11:15
À : firebird-support@yahoogroups.com
Objet : Re: Re: [firebird-support] How To Get Entire Linked Group Details

 

 

Hi Norbert,

 

The SQL you have shared without CTE, gives me other group details as well if they exists. If you create another base group like 'Vishal Group', then this SQL gives that group details as well. I tried but putting group name in condition but getting only one record, could you show me how to get only 'Vishal Group' records, so that would also a good learning for me.

 

 

Thanks In Advance.

 

With Best Regards.

 

Vishal

 

 

On Friday, 2 October 2015 6:57 PM, "'Norbert Saint Georges' nsg@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:

 

 

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}