I have 4 tables, from which 2 are necessary for this problem:
CREATE TABLE Ingredient_sets
(
recipe_id NUMBER(3,0),
ingred_id NUMBER(3,0),
quantity NUMBER(5,2),
um VARCHAR2(10) NOT NULL),
comments VARCHAR2(100)
);
CREATE TABLE Ingredient
(
ingred_id NUMBER(3,0),
ingredient VARCHAR2(30)
);
I have to make a join statement. It should display ingredient1, ingredient2, where the ingredients are in the same recipe (so recipe_id
is the same), have the same unit of measurement (um
), and the same quantity.
The result-pair should appear only once!
ingred_id
is the primary key in the Ingredient
table and foreign key in the Ingredient_sets
table:
ALTER TABLE Ingredient
MODIFY (CONSTRAINT ingred_id_pk PRIMARY KEY (ingred_id));
ALTER TABLE Ingredient_sets
MODIFY (CONSTRAINT ingred_id_fk FOREIGN KEY (ingred_id)
REFERENCES Ingredient(ingred_id) ON DELETE CASCADE);
Recipe_id
is the primary key for its table.
Until now I tried different join statements, but nothing is working
CodePudding user response:
If the table Ingredient_sets
had a primary key the solution would be simpler. Without it, the query you can use DISTINCT
to remove duplicates. The query can look like:
select distinct a.recipe_id, a.ingred_id, a.um, a.quantity
from Ingredient_sets a
join Ingredient_sets b
on a.recipe_id = b.recipe_id
and a.ingred_id = b.ingred_id
and a.um = b.um
and a.quantity = b.quantity
CodePudding user response:
Use a self-join on equal recipe_id & um & quantity.
And also on a higher (or lower) ingred_id.
Then you get the duo's only once.
Sample data
CREATE TABLE Ingredient ( ingred_id NUMBER(3,0), ingredient VARCHAR2(30), CONSTRAINT ingred_id_pk PRIMARY KEY (ingred_id) ); CREATE TABLE Ingredient_sets ( recipe_id NUMBER(3,0), ingred_id NUMBER(3,0), quantity NUMBER(5,2), um VARCHAR2(10) NOT NULL, comments VARCHAR2(100), CONSTRAINT ingred_id_fk FOREIGN KEY (ingred_id) REFERENCES Ingredient(ingred_id) ON DELETE CASCADE ); INSERT ALL INTO Ingredient VALUES (1, 'Butter') INTO Ingredient VALUES (2, 'Milk') INTO Ingredient VALUES (3, 'Egg') SELECT 1 FROM DUAL; INSERT INTO Ingredient_sets (recipe_id, ingred_id, quantity, um, comments) SELECT 1, 1, 20.2, 'gram', 'greasy stuff' FROM DUAL UNION ALL SELECT 1, 2, 20.2, 'gram', 'liquid stuff' FROM DUAL UNION ALL SELECT 1, 3, 2.0, 'piece', 'slimey stuff' FROM DUAL
Query:
select set1.recipe_id , set1.um , set1.quantity , set1.ingred_id as ingred_id1 , ing1.ingredient as ingredient1 , set2.ingred_id as ingred_id2 , ing2.ingredient as ingredient2 from Ingredient_sets set1 join Ingredient_sets set2 on set2.recipe_id = set1.recipe_id and set2.um = set1.um and set2.quantity = set1.quantity and set2.ingred_id > set1.ingred_id left join Ingredient ing1 on ing1.ingred_id = set1.ingred_id left join Ingredient ing2 on ing2.ingred_id = set2.ingred_id
RECIPE_ID | UM | QUANTITY | INGRED_ID1 | INGREDIENT1 | INGRED_ID2 | INGREDIENT2 --------: | :--- | -------: | ---------: | :---------- | ---------: | :---------- 1 | gram | 20.2 | 1 | Butter | 2 | Milk
Or use a COUNT OVER
to filter those that don't have unique um & quantity per set.
select * from ( select ingset.* , ing.ingredient , count(*) over (partition by recipe_id, um, quantity) as same from Ingredient_sets ingset left join Ingredient ing on ing.ingred_id = ingset.ingred_id ) q where same > 1 order by recipe_id, um, quantity
RECIPE_ID | INGRED_ID | QUANTITY | UM | COMMENTS | INGREDIENT | SAME --------: | --------: | -------: | :--- | :----------- | :--------- | ---: 1 | 1 | 20.2 | gram | greasy stuff | Butter | 2 1 | 2 | 20.2 | gram | liquid stuff | Milk | 2
db<>fiddle here