Recursive query does not return records when the parent product and the child product have the same code

Question

Recursive query does not return records when the parent product and the child product have the same code

Navigation

#1 by (0 votes)

0

I have a table that maps a production line. Home The fields are:
Product: Output product of the machine. Home ParentProduct: Input product. Home Machine: Machine code. Home I'm using CTE's to get the data recursively. This works when the Product and ParentProduct codes are different.

use DB_TEST

DECLARE @TableTest TABLE ( 
    Product nvarchar(10) null,
    ParentProduct  nvarchar(10) null,
    Machine nvarchar(10) null
)

begin

    insert into @TableTest values ('Product1', null, 'MACH1' );
    insert into @TableTest values ('Product2', null, 'MACH2'  );
    insert into @TableTest values ('Product3', null, 'MACH3'  );
    insert into @TableTest values ('Product4', 'Product1', 'MACH1' );
    insert into @TableTest values ('Product5', 'Product1', 'MACH1' );
    insert into @TableTest values ('Product6', 'Product2', 'MACH2' );
    insert into @TableTest values ('Product7', 'Product4', 'MACH2' );
    insert into @TableTest values ('Product8', 'Product4', 'MACH3' );
    insert into @TableTest values ('Product9', 'Product5', 'MACH1' );
    insert into @TableTest values ('Product10', 'Product6', 'MACH2' );
    insert into @TableTest values ('Product11', 'Product8', 'MACH3' );

;WITH tree AS
(
    SELECT P.Machine, p.Product, p.ParentProduct, [level] = 1, [path] = cast(p.Product as varchar(100))
    FROM @TableTest p
    WHERE p.ParentProduct IS NULL
    UNION ALL
    SELECT P2.Machine, p2.Product, p2.ParentProduct,  [level] = tree.[level] + 1, 
           [path] = Cast(tree.[path] + ' > ' + cast(p2.Product as varchar(10)) as varchar(100))
    FROM @TableTest p2 INNER JOIN tree ON tree.Product = p2.ParentProduct 
)
SELECT * FROM tree 
Order by machine
OPTION (MAXRECURSION 0)

end;

Correct Score.

Butwhenthecodesarethesame(ParentProduct=Product)theproductdoesnotappearintheresult.

Inthisexampletheoutputproduct'product8'hasthesamecodeastheinputproduct.(Thisisarulehere.)Someproductsmayhavethesameinputandoutputcode.)

insertinto@TableTestvalues('Product1',null,'MACH1');insertinto@TableTestvalues('Product2',null,'MACH2');insertinto@TableTestvalues('Product3',null,'MACH3');insertinto@TableTestvalues('Product4','Product1','MACH1');insertinto@TableTestvalues('Product5','Product1','MACH1');insertinto@TableTestvalues('Product6','Product2','MACH2');insertinto@TableTestvalues('Product7','Product4','MACH2');/*----AQUIOPRODUTODEENTRADAÉIGUALAODESAÍDA.-----*/insertinto@TableTestvalues('Product8','Product8','MACH3');insertinto@TableTestvalues('Product9','Product5','MACH1');insertinto@TableTestvalues('Product10','Product6','MACH2');insertinto@TableTestvalues('Product11','Product8','MACH3');

ResultINCORRECT.
Product8anditsrelationshipsdisappearfromthequeryresult.

I needed to show the 'path' or the path, like a breadcrumb, of product 'product8' like this:
Product1 > Product4 > Product8 > Product8 > Product11

The product needs to appear 2 times on the way.
Is it possible to get this result? How should I proceed?

sql-server relacionamento path

asked by anonymous 23.04.2018 / 15:00

1 answer

Add image to QR code generated by ZXing.Net C # MVC Best way to transfer files between the cloud and a proprietary environment

score 0 · Answer 1

First highlight that the result for the second data list is not wrong. The link between Product 8 and your ParentProduct (s) does not exist, or has disappeared, between the first list of inserts and the second list, so they are not included in the query result. I also understand the reason for missing, because if the connection existed the query looped, because it would be "stuck" in the iterations between product and parentproduct.

Possible solution follows the example:

DECLARE @TableTest TABLE ( 
    Product nvarchar(10) null,
    ParentProduct  nvarchar(10) null,
    Machine nvarchar(10) null
)

begin

    insert into @TableTest values ('Product1', null, 'MACH1' );
    insert into @TableTest values ('Product2', null, 'MACH2'  );
    insert into @TableTest values ('Product3', null, 'MACH3'  );
    insert into @TableTest values ('Product4', 'Product1', 'MACH1' );
    insert into @TableTest values ('Product5', 'Product1', 'MACH1' );
    insert into @TableTest values ('Product6', 'Product2', 'MACH2' );
    insert into @TableTest values ('Product7', 'Product4', 'MACH2' );
    insert into @TableTest values ('Product8', 'Product4', 'MACH3' );
    insert into @TableTest values ('Product8', 'Product8', 'MACH3' );
    insert into @TableTest values ('Product9', 'Product5', 'MACH1' );
    insert into @TableTest values ('Product10', 'Product6', 'MACH2' );
    insert into @TableTest values ('Product11', 'Product8', 'MACH3' );

;WITH tree AS
(
    SELECT P.Machine, p.Product, p.ParentProduct, [level] = 1, [path] = cast(p.Product as varchar(100))
    FROM @TableTest p
    WHERE p.ParentProduct IS NULL
    UNION ALL
    SELECT P2.Machine, p2.Product, p2.ParentProduct,  [level] = tree.[level] + 1, 
           [path] = Cast(tree.[path] + ' > ' + cast(p2.Product as varchar(10)) as varchar(100))
    FROM @TableTest p2 INNER JOIN tree ON tree.Product = p2.ParentProduct 
        AND (tree.Product <> p2.Product or tree.Machine <> p2.Machine OR tree.ParentProduct <> p2.ParentProduct)
)
SELECT * FROM tree 
Order by machine
OPTION (MAXRECURSION 0)

end;

Note that only two lines have been added to your source code, one in the inserts to create the relationship between product and your "parent" and one to break the "loop" in the union when product and parent are the same in the clause Onde. I hope it helps!