I have a "junction" table (recipe_ingredients) that I'm using to handle a many-to-many relationship in my database. Each field in that table is a reference to the PK of another table: recipe_id, ingredient_id, quantity_id, and measure_id. These are the PKs of the metadata, ingredients, quantities, and measurements tables. With the exception of the metadata table, all these tables have only two fields: the PK, and a name or number associated with that PK.
I'm trying to query recipe_ingredients for all rows that have a given recipe_id... but instead of displaying the PKs in each row with that recipe_id, I want to display the name/number associated with that PK in that table.
So instead of a query that returns this:
recipe_id | ingredient_id | quantity_id | measure_id
----------+---------------+-------------+-----------
1 | 10 | 2 | 3
----------+---------------+-------------+-----------
1 | 12 | 2 | 3
----------+---------------+-------------+-----------
1 | 13 | 5 | 6
----------+---------------+-------------+-----------
...
I'm looking for a query that returns something like this:
recipe_id | ingredient_name | quantity_num | measure_num
----------+-----------------+--------------+------------
1 | Ground cayenne | 1/2 | tsp
----------+-----------------+--------------+------------
1 | Ground paprika | 1/2 | tsp
----------+-----------------+--------------+------------
1 | Olive oil | 1 | tbsp
----------+-----------------+--------------+------------
...
I just started learning SQL, so I only know how to make simple queries to a single table. I don't even know where to begin with this query, except that I may need a kind of join statement. What query could I write to achieve this?
I'm using sqlite3 on Debian.
The recipe_ingredients "junction" table:
CREATE TABLE recipe_ingredients (
recipe_id int,
ingredient_id int,
quantity_id int,
measure_id int,
primary key (recipe_id, ingredient_id),
foreign key (recipe_id)
references metadata (recipe_id)
on update restrict
on delete restrict,
foreign key (ingredient_id)
references ingredients (ingredient_id)
on update restrict
on delete restrict,
foreign key (quantity_id)
references quantities (quantity_id)
on update restrict
on delete restrict,
foreign key (measure_id)
references measurements (measure_id)
on update restrict
on delete restrict
);
These are the foreign tables referenced by the junction table:
create table if not exists ingredients (
ingredient_id integer primary key,
ingredient_name text not null
);
create table if not exists quantities (
quantity_id integer primary key,
quantity_num int not null
);
create table if not exists measurements (
measure_id integer primary key,
measure_name text not null
);
create table if not exists metadata (
recipe_id integer primary key,
recipe_name text not null,
course_id int,
cuisine_id int,
servings int,
prep_time int,
cook_time int,
total_time int,
foreign key (course_id)
references courses (course_id)
on update restrict
on delete restrict,
foreign key (cuisine_id)
references cuisine (cuisine_id)
on update cascade
on delete cascade
);
You can join the junction table with each of the related tables like so:
select
ri.recipe_id,
i.ingredient_name,
q.quantity_num,
m.measure_num
from recipe_ingredients ri
inner join ingredients i on i.ingredient_id = ri.ingredient_id
inner join quantities q on q.quantity_id = ri.quantity_id
inner noin measurements m on m.measure_id = ri.measure_id
where ri.recipe_id = ?
The question mark (?) stands for the recipe_id that you are looking for.