Creating Association between 2 product IDs

Question

I have to create an association between 2 products, which has unique product_ids and insert them into an already constructed table. The association is created based on unique part number these product ids have. For instance:

Product_id = 7578711
Part Number = 0101-2478

Product Id = 7957948
Part Number = 0101-2478

Product Id = 10558140
Part Number = 0101-2478

and my current table has the following columns:

ID (int) identity (1, 1)
product_id
date
guid

where data is in the form of:

1, 7578711, 12345, 2010-08-24 04:29:04.000,00286AFB-3880-4085-BAA0-DBCC0D59A391

I have a query which has the ability to roll Product_id to part number level and then a query to roll the part number to product_id level.

Based on the above data, where they have same part number, i want to create an association and generate insert statements which will add 2 records in the form of:

2, 7957948, 12345, 2010-08-24 04:29:04.000,00286AFB-3880-4085-BAA0-DBCC0D59A391
3, 10558140, 12345, 2010-08-24 04:29:04.000,00286AFB-3880-4085-BAA0-DBCC0D59A391

There are going to be many product IDs in that table. The above one is just an example:

I have 2 Common Table Expressions: 1 rolls the product Id to part number level, and another rolls back the part number to multiple product Ids. I am trying to avoid a cursor.

Could anyone here help me with this problem?

My 2 CTEs as as follows:

;WITH cte (product_id, item_number)
AS
(

SELECT DISTINCT --TOP 1000
        pds.product_id
    --,pd.productOwner_id
    , i.item_number
FROM    SurfwatcherEndeavorStats.dbo.productDetailBySite pds WITH ( NOLOCK )
        INNER JOIN ProductData.dbo.productDimensions pd with ( NOLOCK ) ON pds.product_id = pd.product_id
        INNER JOIN ProductData.dbo.options o with ( NOLOCK ) ON pds.product_id = o.product_id
        INNER JOIN ProductData.dbo.items i with ( NOLOCK ) ON o.option_id = i.item_id
WHERE   pds.productDetail_date > DATEADD(yyyy, -1, GETDATE())
        AND i.item_number IS NOT NULL
    --AND i.item_number = '0101-3258'
)
SELECT TOP 1 item_number 
FROM cte WITH (NOLOCK)
WHERE product_id = 7957948


;WITH cte1 (product_id, item_number)
AS
(

SELECT DISTINCT --TOP 1000
        pds.product_id
    --,pd.productOwner_id
    , i.item_number
FROM    SurfwatcherEndeavorStats.dbo.productDetailBySite pds WITH ( NOLOCK )
        INNER JOIN ProductData.dbo.productDimensions pd with ( NOLOCK ) ON pds.product_id = pd.product_id
        INNER JOIN ProductData.dbo.options o with ( NOLOCK ) ON pds.product_id = o.product_id
        INNER JOIN ProductData.dbo.items i with ( NOLOCK ) ON o.option_id = i.item_id
WHERE   pds.productDetail_date > DATEADD(yyyy, -1, GETDATE())
        AND i.item_number IS NOT NULL
)
SELECT product_id 
FROM cte1 WITH (NOLOCK)
WHERE item_number = '0101-2478'

Answer 1

try this Sql. it should give you a complete list of all associations between two products that both use the same part number... Is that what you want ?

   Select Distinct A.Product_Id, B.Product_ID
   From YourTable A
      Join YourTable B
         On B.PartNumber = A.PartNumber
            And B.Product_Id > A.Product_Id