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

Question

I have a table that maps a production line.

The fields are:
Product: Machine Output Product.
Parentproduct: Input Product.
Machine: Code of the machine.

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 RESULT.

But when the codes are equal (Parentproduct = Product) the product does not appear in the result.

In this example the output product 'product8' has the same code as the input product. (This is a rule here of the company. Some products may have the same input and output code.)

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' );

/* ---- AQUI O PRODUTO DE ENTRADA É IGUAL AO DE SAÍDA. -----*/
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' );

INCORRECT RESULT.
Product 'product8' and their relationships disappear from consultation result.

I needed to show the path or path, like a breadcrumb, of the product 'product8' that way:
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?

Answer 1

First note that the result for the second data list is not wrong. The link between Product 8 and your (s) Parentproduct(s) does not exist, or has disappeared, between the first list of inserts and the second, so they are not included in the result of the query. I also understand why to miss, because if the link existed the query would loop, as it would be "stuck" in the iterations between product and parentproduct.

As a 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 its "progenitor" and one to break that "loop" in Union when product and Parent are the same in the Where clause. I hope it helps!