Done with one query?

[JOE]

I think it can be done, but have spent too long. Please help if you
can.

I currently have two tables (Product-Sites and BillOfMaterials).
Product-Sites simply enumerates all of the products a particular site
can store/handle. BillOfMaterials is self-explanatory, as it contains
the product requirements (components) to make the final "group." So,
as output, I want a query to produce each Group a DC can make based on
the products it can store/handle. Here is an example:

Table Product-Sites
Prod Site
P1 S1
P2 S1
P3 S1
P3 S2
P4 S2
P1 S3
P3 S3

Table BillOfMaterials
Component FinalGroup
P1 Grp1
P2 Grp1
P3 Grp2
P4 Grp2

Desired Result:
S1 Grp1
S2 Grp2

Make sense? Any help would be much appreciated. Thank you...

 

[Tom Ellison]

Dear Joe:

SELECT B.FinalGroup, P.Site,
(SELECT COUNT(*)
FROM BillOfMaterials B1
WHERE B1.FinalGroup = B.FinalGroup)
AS PartCt,
(SELECT COUNT(*)
FROM BillOfMaterials B1
INNER JOIN [Product-Sites] P1
ON P1.Prod = B1.Component
WHERE B1.FinalGroup = B.FinalGroup
AND P1.SIte = P.Site)
AS GroupCt
FROM BillOfMaterials B
INNER JOIN [Product-Sites] P
ON P.Prod = B.Component
GROUP BY B.FinalGroup, P.Site

The above is illustrative of the technique used, not the final query. The
columns PartCt and GroupCt show the number of the required parts at the
designated Site and the number of required parts in the Group. Now, when
these are equal, then you have enough different parts at that site to
construct the Group.

This presumes one thing: that the rows in Product-Sites and in
BillOfMaterials are each unique. You must never have two rows in
Product-Sites that show that a Prod is at a Site (don't duplicate the
combination of these 2 columns). Similarly, don't allow a
Component/FinalGroup to be duplicated in BillOfMaterials. Since the query
counts the number of each and determines they are the same, it depends on
this fact. Make sense?

So, here's the final query:

SELECT B.FinalGroup, P.Site
FROM BillOfMaterials B
INNER JOIN [Product-Sites] P
ON P.Prod = B.Component
GROUP BY B.FinalGroup, P.Site
HAVING (SELECT COUNT(*)
FROM BillOfMaterials B1
WHERE B1.FinalGroup = B.FinalGroup)
=
(SELECT COUNT(*)
FROM BillOfMaterials B1
INNER JOIN [Product-Sites] P1
ON P1.Prod = B1.Component
WHERE B1.FinalGroup = B.FinalGroup
AND P1.SIte = P.Site)

This gives:

FinalGroup Site
Grp1 S1
Grp2 S2


Does that look right?

Tom Ellison

 

[JOE]

Tom--

I owe you one..that works pretty slick. Your assumptions are valid
(unique records), so thank you very much!

JOE