Sql-server – more efficient way to resolve this “one to many” circular reference

sql server

In our ERP database, there is 1 table for products called PRODUCTS, which contains both kits and single SKU product information, and a table called PRODUCT_KIT_ITEMS that contain the relationships between kit SKUs and their components.

In the PRODUCT table, each kit has a KIT_ID on the parent SKU row and the PRODUCT_KIT_ITEMS table lists each component PRODUCT_ID from the PRODUCTS table and the kit id of the parent SKU associated with it.

What I wanted was a list of all the SKUs of components associated with a parent SKU, both of which would be selected from the same column on the PRODUCTS table.

This was the solution I came up with:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID],[PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child 
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU],[KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent 
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];

I'm a bit of a newbie and was wondering if there was a better way to accomplish this, for instance, without running two sub-queries on the PRODUCTS table.

Best Answer

If this is your current table schema: 

CREATE TABLE PRODUCTS 
(
   PRODUCT_ID INT PRIMARY KEY,
   PRODUCT_SKU INT,
   KIT_ID INT
);

CREATE TABLE PRODUCT_KIT_ITEMS
(
    KIT_ID INT,
    PRODUCT_ID INT REFERENCES PRODUCTS(PRODUCT_ID),

    PRIMARY KEY (KIT_ID, PRODUCT_ID)
);

INSERT INTO PRODUCTS VALUES 
(1, 10, 1), (2, 20, 0), (3, 30, 0), (4, 40, 2),
(5, 50, 0), (6, 60, 0), (7, 70, 0), (8, 80, 0);

INSERT INTO PRODUCT_KIT_ITEMS VALUES
(1, 2), (1, 3), (1, 6), -- KIT_ID(1) HOLDS PRODUCT_ID(2, 3, 6)
(2, 5), (2, 7);         -- KIT_ID(2) HOLDS PRODUCT_ID(5, 7)
GO

13 rows affected

You can get it by selecting all products with KIT_ID <> 0 (Parent rows) and then use CROSS APPLY to select all child rows that match parent KIT_ID.

SELECT      P.PRODUCT_SKU AS PARENT_SKU,
            CHILDS.PRODUCT_SKU AS CHILD_SKU
FROM        PRODUCTS P
CROSS APPLY (SELECT P2.PRODUCT_SKU
             FROM   PRODUCT_KIT_ITEMS PKI
             JOIN   PRODUCTS P2
             ON     P2.PRODUCT_ID = PKI.PRODUCT_ID
             WHERE  PKI.KIT_ID = P.KIT_ID) CHILDS
WHERE        COALESCE(KIT_ID, 0) <> 0
ORDER BY     P.PRODUCT_SKU; 
GO

PARENT_SKU | CHILD_SKU
---------: | --------:
        10 |        20
        10 |        30
        10 |        60
        40 |        50
        40 |        70

Your current query:

SELECT   Parent.[PRODUCT_SKU] AS KitParent,
         Child.[PRODUCT_SKU] AS ChildSKU
FROM     [PRODUCT_KIT_ITEMS]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU]
          FROM   [PRODUCTS]
         ) AS Child 
ON       Child.[PRODUCT_ID] = [PRODUCT_KIT_ITEMS].[PRODUCT_ID]
JOIN     (
          SELECT [PRODUCT_ID], [PRODUCT_SKU], [KIT_ID]
          FROM   [PRODUCTS]
          WHERE  [KIT_ID] IS NOT NULL
          AND    [KIT_ID] != 0
         ) AS Parent 
ON       Parent.[KIT_ID] = [PRODUCT_KIT_ITEMS].[KIT_ID]
ORDER BY Parent.[PRODUCT_SKU];
GO

KitParent | ChildSKU
--------: | -------:
       10 |       20
       10 |       30
       10 |       60
       40 |       50
       40 |       70

dbfiddle here

Related Question