CODEWITHSUNDEEP
ms-access
jlstrecker
jlstrecker

Reputation: 5033

Join expression not supported for inner join with subquery

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?

Upvotes: 0

Views: 840

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

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

Upvotes: 0

Tomalak
Tomalak

Reputation: 338228

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?

Upvotes: 2

Related Questions