Creating Association - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Creating Association

Author

Topic

nepcrew
Starting Member

2 Posts

Posted - 2010-09-09 : 15:14:36

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?

Thanks in advance.

-Subhash

nepcrew
Starting Member

2 Posts

Posted - 2010-09-09 : 15:17:11

2 CTEs are as follows:

CTE 1

;WITH cte (product_id, item_number)
AS
(
----STAGE TABLE FOR PART NUMBER HITS LAST YEAR

--TRUNCATE TABLE SR_PartNumHitsLastYear
--INSERT SR_PartNumHitsLastYear
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

Based on the fact that this will output:
item_number = 0101-2478

CTE2

;WITH cte1 (product_id, item_number)
AS
(
----STAGE TABLE FOR PART NUMBER HITS LAST YEAR

--TRUNCATE TABLE SR_PartNumHitsLastYear
--INSERT SR_PartNumHitsLastYear
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'

this will out pids:
7578711
7957948
10558140

Any help is greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources