SQL query print two columns from two tables with count

I have a table with recipies and one with ingredients, and one that connects them.

How can I make a query that lists each recipe and the number of ingredients there are in each one?

I have tried using count but I find it hard to work since we are getting data from two tables and have to print two things. Thanks in advance.

CREATE TABLE recipe(
id INTEGER,
title TEXT,
time INTEGER,
difficulty INTEGER
);

CREATE TABLE ingredient(
id INTEGER,
name TEXT
);

CREATE TABLE ingredient_recipe(
id_ingredient INTEGER,
id_recipe INTEGER
);

Edit: I know how to just print out all the counts, but I have no idea how to proceed from this:

SELECT COUNT(*)
FROM ingredient_recipe
GROUP BY id_recipe

Answer

Your query is halfway there. You just need to bring in the recipe table with a join:

SELECT ir.id_recipe, r.title, COUNT(*) cnt_ingredients
FROM ingredient_recipe ir
INNER JOIN recipe r ON r.id = ir.id_recipe
GROUP BY ir.id_recipe, r.title

Advertisement

Answer