Need help with outer-joins

[MadCrow]

Hello everyone.

I was hoping someone could help me understand outer joins better.

I have the following tables set up :

FormulaTable:




I am trying to query these tables so that I get all items from a formula, and if any of the ingredients have “ACID” associated with them in the IngredGroup table, I want that to show. So for this data it would look like:



I put in the following query:

SELECT FormulaTable.FormulaName, FormulaTable.[Item#], FormulaTable.Ingredient, IngredGroup.Group
 
FROM FormulaTable LEFT JOIN (IngredTable RIGHT JOIN (GroupTable RIGHT JOIN IngredGroup ON
                  GroupTable.Group = IngredGroup.Group) ON IngredTable.Ingred = IngredGroup.Ingred) ON
                  FormulaTable.Ingredient = IngredGroup.Ingred
 
WHERE (((FormulaTable.FormulaName)=[X]) AND ((IngredGroup.Group)=[Y]));

If I enter F1 and ACID for the variables, I get this:



If I add:
OR (((FormulaTable.FormulaName)=[X]) AND ((IngredGroup.Group) Is Null)) to WHERE, I get:



So I’m still missing RM4 since it is tagged with “INORG” but not “ACID”. I guess I understand that part, since RM4 is not null. However I thought that with the outer joins I used, I would get everything with FormulaName = “F1”, but it seems to be behaving more like an inner join.

Where have I gone wrong? Any quick suggestions on how to get the output I intended?

Thank you for your time!

[June7]

RM5 in IngredGroup shows as INORG, why is it ACID in the output? If you want to provide db for analysis, follow instructions at bottom of my post.

Why the * character in field names? Is that just for the forum post?

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be ItemNum or ItemNo or Item_Num.

[MadCrow]

Thank you June. Attached is db. To answer your question about RM5, the IngredGroup table is populated as (I think it didn't show right in original post):


I want it so an RM can have many groups associated.

The * was just to designate the primary key, but I changed it to pics now. Hopefully it is clearer.

[June7]

The RM and many groups doesn't make sense to me and will probably cause lots of issues. But consider:

Query1
SELECT FormulaTable.FormulaName, FormulaTable.[Item#], FormulaTable.Ingredient, IngredGroup.Group
FROM IngredTable RIGHT JOIN (FormulaTable LEFT JOIN IngredGroup ON FormulaTable.Ingredient = IngredGroup.Ingred) ON IngredTable.Ingred = IngredGroup.Ingred
WHERE (((IngredGroup.Group)="ACID"));

Query2
SELECT FormulaTable.FormulaName, FormulaTable.[Item#], FormulaTable.Ingredient, FormulaTable.Amount, Query1.Group
FROM Query1 RIGHT JOIN FormulaTable ON (Query1.[Item#] = FormulaTable.[Item#]) AND (Query1.FormulaName = FormulaTable.FormulaName);

[MadCrow]

That seems to work perfectly! Thank you very much June!

As for the way I have my groups, I will have an unlimited number of groups I may want to assign to any particular Ingredient. I though this was the cleanest way to do it, but I will re-think that now.