RESOLVED: Matching similar details

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 2008 Forums

Transact-SQL (2008)

RESOLVED: Matching similar details

Author

Topic

soaphope
Starting Member

13 Posts

Posted - 2011-03-24 : 10:18:46

I have products that are made up of components. Each product can contain any amount of one or more components. Each component has a wholesale price and a maker (manufacturer). To store this information, I have a products table, a components table, and a details table that lists the components and their quantities for each product.

I need to be able to determine which products have the same underlying maker, component type and wholesale cost structure. In other words, if product 1 is made of one $3.50 widget from Abe and two $3.50 widgets from Bob, I need all other products that are made of one $3.50 type Q widget from Abe and two $3.50 type Z widgets from Bob. The widgets do not have to be the exact same widget, they just have to be the same type, same wholesale cost and from the same maker.

I've written the queries to isolate the maker, component type, quantity, and wholesale cost data by product, but I'm having trouble creating the SQL statement needed to select all the matching sets.

DECLARE @findthis int
SELECT @findthis = 202

;

WITH T1 AS (
SELECT pdc.productdbuid product,
SUM(pdc.componentcount) compcount,
c.makerdbuid maker,
c.comptypedbuid comptype,
c.wholesaleprice compwsprice
FROM ProductDetailsComponents pdc
INNER JOIN Components c ON pdc.componentdbuid = c.dbuid
WHERE pdc.productdbuid = @findthis
GROUP BY pdc.productdbuid, c.makerdbuid, c.comptypedbuid, c.wholesaleprice
)
,
T2 AS (
SELECT pdc.productdbuid product,
SUM(pdc.componentcount) compcount,
c.makerdbuid maker,
c.comptypedbuid comptype,
c.wholesaleprice compwsprice
FROM ProductDetailsComponents pdc
INNER JOIN Components c ON pdc.componentdbuid = c.dbuid
GROUP BY pdc.productdbuid, c.makerdbuid, c.comptypedbuid, c.wholesaleprice
)

SELECT DISTINCT product FROM T2
WHERE ***all the rows in T1 match a row in the T2 product subset*** AND
***all the rows in the T2 product subset match a row in T1***

In this case T1 is:
product compcount maker comptype compwsprice
202 1 7 1 4.00
202 1 7 5 3.25

And some sample data from T2 is:

198 5 2 1 3.00
199 5 2 1 3.00
200 1 7 1 4.00
200 1 7 5 3.25
201 1 7 1 4.00
201 1 7 5 3.25
202 1 7 1 4.00
202 1 7 5 3.25
211 1 3 2 6.75
211 1 3 3 3.50
211 2 12 10 0.00
211 3 12 45 0.00

What I need for output are the three matching subsets:
200
201
202

Any help with the WHERE, or a different approach, is appreciated!

soaphope
Starting Member

13 Posts

Posted - 2011-03-24 : 11:48:50

Actually I resolved this (I think) with help from here: http://www.bigresource.com/Tracker/Track-ms_sql-8i9gU5pO/

I post the resolution for anyone else who needs help with the problem: finding all matching groups of detail records.

WITH T AS (
SELECT pdc.productdbuid product,
SUM(pdc.componentcount) compcount,
c.makerdbuid maker,
c.comptypedbuid comptype,
c.wholesaleprice compwsprice,
COUNT(pdc.productdbuid) OVER (PARTITION BY (select productdbuid)) setsize
FROM ProductDetailsComponents pdc
INNER JOIN Components c ON pdc.componentdbuid = c.dbuid
GROUP BY pdc.productdbuid, c.makerdbuid, c.comptypedbuid, c.wholesaleprice
)

SELECT T2.product from T T2 join T T1 on
T2.compcount = T1.compcount AND
T2.maker = T1.maker AND
T2.comptype = T1.comptype AND
T2.compwsprice = T1.compwsprice AND
T2.setsize = T1.setsize AND
T1.product = @findthis
GROUP BY T2.product
HAVING COUNT(T2.product) = (SELECT COUNT(product) FROM T WHERE product = @findthis)

Salah Boukadoum
Founder, Soap Hope
Where 100% of profits go to lift women from poverty - http://soaphope.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources