MySQL - Relational/intermediary tables and JOIN queries?

Been reading up on this (for ages!) and can't get clear picture.

First, if I have two tables (e.g. recipes and ingredients) with a many-to-many relationship and I create a intermediary/relational table, how do I write an SQL query to to find all recipes with, say, bananas in them?

Second, why would I have this third relational table if I can find the same information using JOIN queries without the third tables creation??

Really appreciate a clear, helpful explanation, thanks.

Solution

how do I write an SQL query to to find all recipes with, say, bananas in them?

You can do:

select distinct r.id, r.name
from recipe r
join recipe_ingredient ri on ri.id_recipe = r.id
join ingredient i on i.id = ri.id_ingredient
where i.name = 'banana'

Second, why would I have this third relational table if I can find the same information using JOIN queries without the third tables creation?

Since a recipe can have many ingredients, and an ingredient can be related to many recipies the relationship between those two tables is not 1:n but n:m. Therefore, you need an intermediate table, as shown below:

create table recipe (
  id int primary key not null,
  name varchar(20)
);

create table ingredient (
  id int primary key not null,
  name varchar(20)
);

create table recipe_ingredient (
  id int primary key not null,
  id_recipe int not null,
  id_ingredient int not null,
  quantity double not null,
  foreign key fk1 (id_recipe) references recipe (id),
  foreign key fk2 (id_ingredient) references ingredient (id)
);

If an ingredient showed up in a single recipe always, the structure would be simpler, as you seem to think. This simpler structure would probably look like:

create table recipe (
  id int primary key not null,
  name varchar(20)
);

create table ingredient (
  id int primary key not null,
  name varchar(20),
  id_recipe int not null,
  foreign key fk3 (id_recipe) references recipe (id) 
);

A model like this one is not really practical in this case. You would end up having the same ingredient multiple times. For example, if a cake uses "flour" and bread uses "flour", then "flour" would end up twice in the ingredients table. Not a great idea.