select groups that contain all records from another group

Question

select groups that contain all records from another group

Allen Nugent

Right there with Babe

Points: 734
More actions
August 10, 2006 at 9:53 pm

#67626

I'm trying to select all kits (field name = KitItem) that contain a set of part numbers specified in a table called "Find Items In Kits". The source query has a record for each part number, and one of its fields is KitItem. (So, a given value of KitItem appears in N rows, where N is the number of unique part numbers in that kit.)
This isn't right:
SELECT [Sub Kit Comps].KitItem FROM [Sub Kit Comps]
WHERE ((SELECT COUNT (1) FROM [Find Items In Kits])
=
(SELECT COUNT (1) FROM [Sub Kit Comps] WHERE [Sub Kit Comps].Item IN (SELECT [Find Items In Kits].[Part Number] FROM [Find Items In Kits])));
I'm going mental trying to work this out. Can anyone help?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Stephen Turner Mr or Mrs. 500 Points: 540 More actions · Answer 1

To clarify what you need... Two tables Parts and Kits with a relation where each KitID has many Parts. Third table FindLists also related to Parts, where each FindListID has many Parts. You want to find all Kits that contain all Parts for a given FindListID?

I'd get a list of all parts and kits then filter them for parts that only appear in the parts list I want. Count the number of parts grouped by kit. Then filter so the Count of kit parts matches the parts on the find list.

Steve.

Allen Nugent Right there with Babe Points: 734 More actions · Answer 2

Sounds good, Stephen. But I'm still stuck on the 1st step of your plan: "... filter them for parts that only appear in the parts list I want". In my 1st example, there are only 2 parts in FindList, and I want to select all kits from [Sub Kit Comps] that contain both of these parts. In later examples, there may be as many as 10 parts in FindList, so that is why I don't hard-code the part numbers.

The FindList table (which is the result of a previous query that restricts the kits to ~4,000) looks something like this:

kit1 partnumberA

kit1 partnumberB

...

kit1 partnumberK

kit2 partnumberX

kit2 partnumberY

...

where X, Y etc. could be equal ro A, B, etc. (i.e. the partnumbers are repeated wherever they appear in a new kit).

I just can't get to 1st base, as I seem to be applying circular logic in my SELECT statements.

I also have a table (query) that lists the unique kit names (i.e. it is a subset of [Sub Kit Comps] obtained by using the DISTINCT keyword), if that is of any use.

Thanks for your interest!

Stephen Turner Mr or Mrs. 500 Points: 540 More actions · Answer 3

I'm making a few assumptions about the structure of your data here:

SELECT KitID FROM (

SELECT Count(1) as CountParts, KitID FROM (
SELECT * FROM Parts INNER JOIN KitParts ON PartID = PartID
WHERE PartID IN (SELECT PartID FROM FindList WHERE FindListID = 101)
) GROUP BY KitID

) WHERE CountParts = (SELECT Count(1) FROM FindList WHERE FindListID = 101)

This code it probably very slow but it serves a purpose. The third select statement is really the first step, a filtered list of all parts and kits. Then I do the count in the next level out. Finally looking for the right count in the last level out.

Of course if your data is structured differently, then you may not be able to get it working...

Allen Nugent Right there with Babe Points: 734 More actions · Answer 4

Thankyou very much, Stephen.

I have barely started testing (got sidetracked by the need to start documenting big-time), but it looks like it works!

Allen Nugent Right there with Babe Points: 734 More actions · Answer 5

Stephen,

Your SQl code worked like a charm. Thanks a zillion!