CODEWITHSUNDEEP
sqlms-accessjoinleft-join
Tourtelot
Tourtelot

Reputation: 137

Access 'Join not supported' Moving LEFT Join

I've done some research here and I understand that in Access nested joins cause issues.

I believe that is the issue in the first example. 

SELECT 
Recipe_Classes.RecipeClassDescription,
Recipes.RecipeTitle,
Recipes.Preparation,
Ingredients.IngredientName,
Recipe_Ingredients.RecipeSeqNo,
Recipe_Ingredients.Amount,
Measurements.MeasurementDescription
FROM (((
Recipe_Classes
LEFT JOIN Recipes
ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID)
INNER JOIN Recipe_Ingredients
ON Recipes.RecipeID = Recipe_Ingredients.RecipeID)
INNER JOIN Ingredients
ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID)
INNER JOIN Measurements
ON Measurements.MeasureAmountID = Recipe_Ingredients.MeasureAmountID
ORDER BY RecipeTitle, RecipeSeqNo;

I made an attempt to remove the nesting and created a right join in this example

 SELECT 
    Recipe_Classes.RecipeClassDescription,
    Recipes.RecipeTitle,
    Recipes.Preparation,
    Ingredients.IngredientName,
    Recipe_Ingredients.RecipeSeqNo,
    Recipe_Ingredients.Amount,
    Measurements.MeasurementDescription
    FROM (((
    Ingredients
    INNER JOIN Recipe_Ingredients
    ON Ingredeints.IngredientID = Recipe_Ingredients.IngredientID)
    INNER JOIN Measurements
    ON Measurements.MeasureAmountID = Recipe_Ingredients.MeasureAmountID)
    INNER JOIN Recipes
    ON Recipes.RecipeID = Recipe_Ingredients.RecipeID)
    RIGHT JOIN Recipe_Classes
    ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID
    ORDER BY RecipeTitle, RecipeSeqNo;

Can anyone point me in the right direction?

Upvotes: 0

Views: 100

Answers (2)

Parfait
Parfait

Reputation: 107687

Since your main focal point table is Recipe_Ingredients, consider starting with this table in FROM and then JOIN the other parent tables and even nest the Recipes and Recipe_Classes pair:

SELECT 
    c.RecipeClassDescription,
    r.RecipeTitle,
    r.Preparation,
    i.IngredientName,
    ri.RecipeSeqNo,
    ri.Amount,
    m.MeasurementDescription
FROM 
  ((Recipe_Ingredients ri    
    INNER JOIN (Recipes r 
                RIGHT JOIN Recipe_Classes c
                      ON c.RecipeClassID = r.RecipeClassID)
          ON r.RecipeID = ri.RecipeID)
    INNER JOIN Ingredients i
          ON i.IngredientID = ri.IngredientID)
    INNER JOIN Measurements m
          ON m.MeasureAmountID = ri.MeasureAmountID

ORDER BY r.RecipeTitle, ri.RecipeSeqNo;

Of course this is untested without data. Due to Access' JOIN requirements like parnetheses, it is often recommended for new users to build complex queries with MS Access' GUI Query Design then modify generated SQL as needed.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270341

The issue may be the left join that is not needed. Try this from clause:

SELECT Recipe_Classes.RecipeClassDescription,
       Recipes.RecipeTitle,
       Recipes.Preparation,
       Ingredients.IngredientName,
       Recipe_Ingredients.RecipeSeqNo,
       Recipe_Ingredients.Amount,
       Measurements.MeasurementDescription
FROM (((Recipes LEFT JOIN
        Recipe_Classes
        ON Recipe_Classes.RecipeClassID = Recipes.RecipeClassID
       ) LEFT JOIN
       Recipe_Ingredients
       ON Recipes.RecipeID = Recipe_Ingredients.RecipeID
      ) LEFT JOIN
      Ingredients
      ON Ingredients.IngredientID = Recipe_Ingredients.IngredientID
     ) LEFT JOIN Measurements
     ON Measurements.MeasureAmountID = Recipe_Ingredients.MeasureAmountID
ORDER BY RecipeTitle, RecipeSeqNo;

Once you start with LEFT JOINs, generally all the remaining joins should also be LEFT JOINs.

Upvotes: 1

Related Questions