SQL Server : Recursive Advanced Query on two conditional logics Not showing hierarchy relationship

Question

Recently I got a challenge to resolve in SQL Server 2012. This is the background of the problem.

We are maintaining a self referencing entity (hierarchy) in our product entity. Each product is having parent and child relationship.

Product is having special grouping called master products which derive based on the following logic.

If there is only one product available on hierarchy with regardless of IsMasterProdcut flag it consider master account.

For other products which its immediate parent product tagged as master product or top most product which comes first consider as master product.

Graphical representation is as follow ,

This is the DDL

-- Create Table 
CREATE TABLE Product
(
    ProductID int PRIMARY KEY,
    Name      VARCHAR(30) NOT NULL,
    ParentId  int,
    IsMasterProdcut bit NOT NULL
)

-- Insert the data to the table 
-- Senario where top most product is the master product 
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (1,'Prodcut 1',NULL,0); -- <-- this is the master prodcut as non of the child as flaged
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (2,'Prodcut 2',1,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (3,'Prodcut 3',2,0);

-- Senario two where in middnle account has flag as master product 
INSERT INTO Product (ProductID,name,ParentId,IsMasterProdcut) VALUES (4,'Prodcut 4',NULL,0); -- <-- this is the master prodcut as this is top most in hirerachy . So 4 will be master prodcut of 4 and 5 , 6 and 7 will not master product
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (5,'Prodcut 5',4,0);  
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (6,'Prodcut 6',5,1); -- < -- this a a master prodcut as it is flagged as master product , So account 7 and 6 master product with be 6
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (7,'Prodcut 7',6,0);

-- Senario three where it has one product 
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (8,'Prodcut 8',0,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (9,'Prodcut 9',0,1);

-- Senario 4 Complex product 

INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (10,'Prodcut 10',0,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (11,'Prodcut 11',10,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (12,'Prodcut 12',11,1);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (13,'Prodcut 13',12,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (14,'Prodcut 14',10,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (15,'Prodcut 15',14,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (16,'Prodcut 16',15,0);
INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) VALUES (17,'Prodcut 17',10,0);

Expected Result

Master Product ID   Master Product Name Product ID  Product Name
1                    Product 1          1            Product 1
1                    Product 1          2            Product 2
1                    Product 1          3            Product 3
4                    Product 4          4            Product 4
4                    Product 4          5            Product 5
6                    Product 6          6            Product 6
6                    Product 6          7            Product 7
8                    Product 8          8            Product 8
9                    Product 9          9            Product 9
10                   Product 10        10            Product 10 
10                   Product 10        11            Product 11
12                   Product 12        12            Product 12
12                   Product 12        13            Product 13
10                   Product 10        14            Product 14
10                   Product 10        15            Product 15
10                   Product 10        16            Product 16
10                   Product 10        17            Product 17

Working solution: this is the solution that I have got so far:

      BEGIN 

    CREATE TABLE #TmpMasterProduct
    (
       ProductId nvarchar(50)
    )

    INSERT INTO #TmpMasterProduct
    SELECT ProductId

    FROM (
        -- Get master accounts which are flagged as master product  
        SELECT MA.ProductId 
        FROm [dbo].[Product] AS MA WITH (NOLOCK) 
        WHERE MA.[IsMasterProdcut] = 1

        UNION
        -- Get top most prodcut which will be automatically consider as master product.

        SELECT MAT.ProductId 
        FROM DBO.[Product] As MAT WITH (NOLOCK) 
        WHERE MAT.[ParentId] IS NULL
    ) AS MasterProdcuts;


    WITH Mapping as
    (
      SELECT A.ProductId , A.ParentId
      FROM DBO.[Product] A
      WHERE  A.ProductId IN 
      (
        SELECT ProductId 
        FROM #TmpMasterProduct
      )

      UNION ALL

      SELECT A.ProductId , A.ParentId
      FROM DBO.[Product]  A
      INNER JOIN Mapping M
      ON M.ProductId = A.ParentID
     )


    SELECT  M.ParentId As MasterProductId ,  MP.Name As MasterProductName , M.ProductId As ProdcutId , CP.Name As ProductName
    From Mapping As M
    LEFT OUTER JOIN DBO.Product As MP ON MP.ProductId = M.ParentId 
    LEFT OUTER JOIN DBO.Product As CP On CP.ProductId = M.ProductId

    DROP TABLE #TmpMasterProduct

END 

But I'm deviating form the result that I want. This is the current out put I'm getting.

MasterProductId MasterProductName   ProdcutId   ProductName
NULL               NULL               1           Prodcut 1
NULL               NULL               4           Prodcut 4
5                  Prodcut 5          6           Prodcut 6
0                  NULL               9           Prodcut 9
11                 Prodcut 11         12          Prodcut 12
12                 Prodcut 12         13          Prodcut 13
6                  Prodcut 6          7           Prodcut 7
4                  Prodcut 4          5           Prodcut 5
5                  Prodcut 5          6           Prodcut 6
6                  Prodcut 6          7           Prodcut 7
1                  Prodcut 1          2           Prodcut 2
2                  Prodcut 2          3           Prodcut 3

Basically this query I wrote does not goes to deeper level. It terminates form the parent. And second observation I had is this does pick up parents nodes which are not master product.

Is my approach wrong? What is the best way I can get this done except cursors.

Answer 1

You are on the right track :) Some fixes:

You are inserting zero, not null, as ParentId into products 8 and 10 - this is why they are never picked up in your initial #TmpMasterProduct query - you'll need to change these back to NULL e.g.

INSERT INTO Product (ProductID,Name,ParentId,IsMasterProdcut) 
VALUES (8,'Prodcut 8',NULL,0);

(Also, if you were enforcing referential integrity with a Foreign Key from ParentId -> ProductId on the self-join, this kind of issue would be prevented)

You don't need the UNION on the master product prefilter - you can just use OR, i.e.:

INSERT INTO #TmpMasterProduct
SELECT MA.ProductId
FROM 
    [dbo].[Product] AS MA
WHERE MA.[IsMasterProdcut] = 1 OR MA.[ParentId] IS NULL;

In your recursive CTE, you'll need to keep remembering the actual MasterProductId, not necessarily the ParentId for each Master product tree, to allow for hierarchies greater than 1 deep, i.e.

WITH Mapping as
(
  SELECT A.ProductId as MasterProductId, A.ProductId , A.ParentId ...

  UNION ALL

  SELECT M.MasterProductId, A.ProductId , A.ParentId ...

You'll need to introduce a termination condition on navigating a tree, which terminates when there is a node which is itself a Master Product (this will be listed separately).

AND A.IsMasterProdcut = 0

You'll want to order the items by MasterProductId to print them out nicely.

SqlFiddle here