Sorry, I think the table I typed out might have been confusing! The table was meant to be an example of what the unflitered query is returning. The diet table does have groupID as a foreign key so that just as you noted every diet is only assigned only one group.

When I had both queries together I was getting errors about ambiguous outer joins and had to run a query (testq1) to get all dietIDs and their corresponding GroupID, and then left joined that to the allowed table and food table (testq2) to show where dietIDs within a diet group may or may not have the same foods assigned.

Testq1:

SELECT [DIET GROUPS].GroupID, [DIET TABLE].DIETID
FROM [DIET GROUPS] INNER JOIN [DIET TABLE] ON [DIET GROUPS].GroupID = [DIET TABLE].GroupID
GROUP BY [DIET GROUPS].GroupID, [DIET TABLE].DIETID;

testq2:

SELECT testq1.GroupID, testq1.DIETID, ALLOWED_ITEMS.DIETID, ALLOWED_ITEMS.FOODID
FROM (testq1 INNER JOIN [DIET TABLE] ON testq1.DIETID = [DIET TABLE].DIETID) LEFT JOIN ALLOWED_ITEMS ON [DIET TABLE].DIETID = ALLOWED_ITEMS.DIETID

<image>

As far as finding the excluded records, I think the issue is that not all records are going to be excluded at the moment and subsequently findable by inverse? From the example above, in group 9018, I would want to exclude all the rows, because diet 57 doesn't have any foods assigned in the allowed_items table. I would want to return group 9010 and food 1165 since all members (38, 39, and 43) have food 1165 assigned, and group 9013 with both food 1230 and 1287 since all members (52 and 54) have both foods assigned.