Join expression not supported for inner join with subquery

Question

In Access 2003, I'm getting a "Join expression not supported" exception for this:

SELECT ID FROM Recipes INNER JOIN 
   (SELECT RecID, COUNT(RecID) AS NumIngredients 
    FROM Ingredients GROUP BY RecID) 
ON RecID = ID

I have two tables, Recipes and Ingredients. Recipes.ID corresponds to foreign key Ingredients.RecID. I want to get the number of rows in Ingredients that correspond to each row in Recipes. Suggestions?

Answer 1

Try without joining on sub-query:

SELECT 
  r.ID AS RecID,
  COUNT(i.ID) AS NumIngredients
FROM 
  Recipes r
  INNER JOIN Ingredients i ON i.RecID = r.ID
GROUP BY
  r.ID

Does that work?

Answer 2

SELECT R.ID, COUNT(I.ID) AS CountOfIngredientRecords
FROM Recipes R INNER JOIN Ingredients I
ON R.ID = I.RecID
GROUP BY R.ID