Recursive select through 2 tables in SQL Server

Question

For a production report in SSRS I need to display the base material in our ERP which can consist of multiple levels.

I have items in a table INVENTPRODMETHOD which have a field ProdMethodId this relates to a INVENTPRODSUPPITEMS table with 'supporting items'. Based on the ProdMethodId found in the frst table I get a SUPPLITEMID which I need to find out what the ProdMethodId for this (new) item is in the first table INVENTPRODMETHOD, etc, etc. Hope you can follow me…

My requirement is that I need to know the base material on the lowest level. For example:

I want to find the base material for item 100. In the DMO_INVENTPRODMETHOD table I can see that the PRODMETHODID = A123

In the DMO_INVENTPRODSUPPITEMS it tells me this PRODMETHODID A123 has a SUPPLITEMID of S200

Now I need to do the same but now for S200. In DMO_INVENTPRODMETHOD I get PRODMETHODID E123 when I look that up in DMO_INVENTPRODSUPPITEMS the SUPPLITEMID is S400

S400 in the DMO_INVENTPRODMETHOD has PRODMETHODID Z123 but that PRODMETHODID does not exist in DMO_INVENTPRODSUPPITEMS

Now I know I need to look for the ITEMID in the DMO_INVENTPRODBASEITEMS table with PRODMETHODID Z123

CREATE TABLE DMO_INVENTPRODMETHOD (
    ITEMID VARCHAR (10) NOT NULL,
    PRODMETHODID VARCHAR (10) NOT NULL,
);

INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('100', 'A123')
INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('S200', 'E123')
INSERT INTO [dbo].[DMO_INVENTPRODMETHOD] VALUES ('S400', 'Z123')



CREATE TABLE DMO_INVENTPRODSUPPITEMS (
    SUPPLITEMID VARCHAR (10) NOT NULL,
    PRODMETHODID VARCHAR (10) NOT NULL,
);

INSERT INTO [dbo].[DMO_INVENTPRODSUPPITEMS] VALUES ('S200', 'A123')
INSERT INTO [dbo].[DMO_INVENTPRODSUPPITEMS] VALUES ('S400', 'E123')


CREATE TABLE DMO_INVENTPRODBASEITEMS (
    ITEMID VARCHAR (10) NOT NULL,
    PRODMETHODID VARCHAR (10) NOT NULL,
);
INSERT INTO [dbo].[DMO_INVENTPRODBASEITEMS] VALUES ('BAAAB10', 'Z123')

Mike

Answer 1

Here is one way to solve this.

First, Normalize values into a Parent/Child table. Next, perform a pretty standard recursive cte over the normalized data. Finally, reverse order to get the last descendant in the chain.

Sql Fiddle

DECLARE @DMO_INVENTPRODMETHOD  TABLE(ITEMID  VARCHAR (10) NOT NULL,PRODMETHODID VARCHAR (10) NOT NULL)
INSERT INTO @DMO_INVENTPRODMETHOD VALUES ('100', 'A123'),('S200', 'E123'),('S400', 'Z123')

DECLARE @DMO_INVENTPRODSUPPITEMS TABLE(SUPPLITEMID  VARCHAR (10) NOT NULL,PRODMETHODID  VARCHAR (10) NOT NULL)
INSERT INTO @DMO_INVENTPRODSUPPITEMS VALUES ('S200', 'A123'),('S400', 'E123')

DECLARE @DMO_INVENTPRODBASEITEMS TABLE(ITEMID VARCHAR (10) NOT NULL,PRODMETHODID VARCHAR (10) NOT NULL)
INSERT INTO @DMO_INVENTPRODBASEITEMS VALUES ('BAAAB10', 'Z123')

DECLARE @ITEMID NVARCHAR(10) = '100'

;WITH CombinedData AS
(
    SELECT DataLevel=1, ParentID=ITEMID, ChildID=PRODMETHODID  FROM @DMO_INVENTPRODMETHOD
    UNION
    SELECT DataLevel=2, ParentID=PRODMETHODID ,ChildID=SUPPLITEMID  FROM @DMO_INVENTPRODSUPPITEMS
    UNION
    SELECT DataLevel=3, ParentID=PRODMETHODID ,ChildID=ITEMID  FROM @DMO_INVENTPRODBASEITEMS
) 
,RecursiveWalk AS
 (
    SELECT  ParentID, ChildID,Level=1 FROM CombinedData WHERE  DataLevel=1 AND ParentID=@ITEMID

    UNION ALL

    SELECT D.ParentID,D.ChildID, Level=R.Level+1
    FROM
        RecursiveWalk R
        INNER JOIN  CombinedData D ON D.ParentID=R.ChildID
)
,ResultReverseOrdered AS
(
    SELECT *, RowNumber = ROW_NUMBER() OVER(ORDER BY Level DESC) FROM RecursiveWalk
)
SELECT
    PRODMETHODID = ParentID,
    Level 
FROM    
    ResultReverseOrdered
WHERE
    RowNumber = 1