MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.27


CREATE TABLE recipes(id integer, name varchar(20));
INSERT INTO recipes VALUES
   (1,'one'),
   (2,'two');
CREATE TABLE recipe_steps(recipe_id integer, step INteger, description varchar(20));
INSERT INTO recipe_steps VALUES
   (1,1,'recipe 1-step1'),
   (1,2,'recipe 1-step2'),
   (1,3,'recipe 1-step3'),
   (2,1,'recipe 2-step1'),
   (2,2,'recipe 2-step2'),
   (2,3,'recipe 2-step3');
CREATE TABLE ingredients_on_recipes(recipe_id INTEGER, ingredient_id INTEGER);
INSERT INTO ingredients_on_recipes VALUES
   (1,42),
   (1,43),
   (1,44),
   (2,10),
   (2,43),
   (2,50);
CREATE TABLE ingredients(ingredient_id INTEGER, description VARCHAR(20));
INSERT INTO ingredienst VALUES
   (10,'ingredient-10'),
   (42,'ingredient-42'),
   (43,'ingredient-43'),
   (44,'ingredient-44'),
   (50,'ingredient-50'),
-- This will return 3 rows, because the are 3 recipe_steps for recipe=1
SELECT *
FROM recipes r
INNER JOIN recipe_steps rs on rs.recipe_id = r.id
WHERE r.id=1


id
name
recipe_id
step
description


1
one
1
1
recipe 1-step1

1
one
1
2
recipe 1-step2

1
one
1
3
recipe 1-step3


-- This will return 9 rows, 
--     because the are 3 recipe_steps for recipe=1
--     and 3 ingredients for recipe=1
--     and 3 * 3 = 9
SELECT *
FROM recipes r
INNER JOIN recipe_steps rs on rs.recipe_id = r.id
INNER JOIN ingredients_on_recipes ir on ir.recipe_id = r.id
WHERE r.id=1


id
name
recipe_id
step
description
recipe_id
ingredient_id


1
one
1
3
recipe 1-step3
1
42

1
one
1
2
recipe 1-step2
1
42

1
one
1
1
recipe 1-step1
1
42

1
one
1
3
recipe 1-step3
1
43

1
one
1
2
recipe 1-step2
1
43

1
one
1
1
recipe 1-step1
1
43

1
one
1
3
recipe 1-step3
1
44

1
one
1
2
recipe 1-step2
1
44

1
one
1
1
recipe 1-step1
1
44
id	name	recipe_id	step	description
1	one	1	1	recipe 1-step1
1	one	1	2	recipe 1-step2
1	one	1	3	recipe 1-step3