CODEWITHSUNDEEP
mysqljoin
branquito
branquito

Reputation: 4064

SQL join over multiple tables

With a statement like this, I can get recipe names along with how many ingredients each one of them has.

SELECT
r.name, COUNT(i.id) as num_ingredients
FROM recipes AS r
    LEFT JOIN recipe_ingredients ON r.id = recipe_ingredients.recipe_id
    LEFT JOIN ingredients AS i ON recipe_ingredients.ingredient_id = i.id
GROUP BY r.id

How would I add chefs.name to my SELECT along with data that I already have?

table schema

Upvotes: 0

Views: 56

Answers (1)

user3714582
user3714582

Reputation: 1940

Try this

SELECT
r.name, COUNT(i.id) as num_ingredients, che.name AS chefs_name
FROM recipes AS r
    LEFT JOIN recipe_ingredients ON r.id = recipe_ingredients.recipe_id
    LEFT JOIN ingredients AS i ON recipe_ingredients.ingredient_id = i.id
    LEFT JOIN chefs AS che ON r.chef_id = che.id
GROUP BY r.id

If I understood, you would like to JOIN chefs table also and select its name.

You have to alias it in the SELECT statement because you select 2 columns with the same name.

I also think that the data type for column recipes.chef_id should be the same as chefs.id (both int(11) or int(255)).

Upvotes: 2

Related Questions