Getting individual counts of a tables column after joining other tables

Question

I'm having problems getting an accurate count of a column after joining others. When a column is joined I would still like to have a DISTINCT count of the table that it is being joined on.

A restaurant has multiple meals, meals have multiple food groups, food groups have multiple ingredients.

Through the restaurants id I want to be able to calculate how many of meals, food groups, and ingrediants the restaurant has.

When I join the food_groups the count for meals increases as well (I understand this is natural behavior I just don't understand how to get what I need due to it.) I have tried DISTINCT and other things I have found, but nothing seems to do the trick. I would like to keep this to one query rather than splitting it up into multiple ones.

SELECT 
    COUNT(meals.id) AS countMeals,
    COUNT(food_groups.id) AS countGroups,
    COUNT(ingrediants.id) AS countIngrediants
FROM 
    restaurants 
INNER JOIN 
    meals ON restaurants.id = meals.restaurant_id
INNER JOIN
    food_groups ON meals.id = food_groups.meal_id 
INNER JOIN
    ingrediants ON food_groups.id = ingrediants.food_group_id     
WHERE 
    restaurants.id='43'
GROUP BY
    restaurants.id

Thanks!

Answer 1

You're going to have to do subqueries, I think. Something like:

SELECT 
    (SELECT COUNT(1) FROM meals m WHERE m.restaurant_id = r.id) AS countMeals,
    (SELECT COUNT(1) FROM food_groups fg WHERE fg.meal_id = m.id) AS countGroups,
    (SELECT COUNT(1) FROM ingrediants i WHERE i.food_group_id = fg.id) AS countGroups
FROM restaurants r

Answer 2

The DISTINCT goes inside the count

SELECT 
    COUNT(DISTINCT meals.id) AS countMeals,
    COUNT(DISTINCT food_groups.id) AS countGroups,
    COUNT(DISTINCT ingrediants.id) AS countIngrediants
FROM 
    restaurants 
INNER JOIN 
    meals ON restaurants.id = meals.restaurant_id
INNER JOIN
    food_groups ON meals.id = food_groups.meal_id 
INNER JOIN
    ingrediants ON food_groups.id = ingrediants.food_group_id     
WHERE 
    restaurants.id='43'
GROUP BY
    restaurants.id

Answer 3

Where were you putting your DISTINCT and on which columns? When using COUNT() you need to do the distinct inside the parentheses and you need to do it over a single column that is distinct for what you're trying to count. For example:

SELECT
    COUNT(DISTINCT M.id) AS count_meals,
    COUNT(DISTINCT FG.id) AS count_food_groups,
    COUNT(DISTINCT I.id) AS count_ingredients
FROM
    Restaurants R
INNER JOIN Meals M ON M.restaurant_id = R.id
INNER JOIN Food_Groups FG ON FG.meal_id = M.id
INNER JOIN Ingredients I ON I.food_group_id = FG.id
WHERE 
    R.id='43'

Since you're selecting for a single restaurant, you shouldn't need the GROUP BY. Also, unless this is in a non-English language, I think you misspelled ingredients.