How can you make a join clause displaying (ingredient1,ingredient2) from the same table

Question

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

Answer 1

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