Compare two tables that have a third table in common

[mchatz13]

Hi,
I have have a "Store Items" table and a "Groups" table, I need to check if a combination of 1 item and all the groups are not in a third table "Group Items" For example, I have item A, and groups 1,2,3. I would need to check all combinations of Item and Group (A1,A2,A3), to see if they are in the "Group Items" table, and report they ones that are not. There are approx. 1600 store items, 4000 groups, and about 7 million Group Items.

Any help would be greatly appreciated

 

[tlbroadbent]

How about something like this? Make sure the tables are indexed. Then sit back and relax as this may take a while.

Select qry.* From
(Select Distinct i.Item, g.Group
From [Store Items] i, Groups g) qry
Left Join [Group Items] gi
On qry.Item=gi.Item
And qry.Group=gi.Group
Where gi.item Is Null
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.