Join operations in SQL-CodePudding

I should retrieve the IDs and names of ingredients that are not contained by any ice cream and then sort the output rows in ascending order by ingredient ID. I don't quite understand how JOIN -operation works in this exercise.

I tried e.g. the following but it gives too many rows in the output.

SELECT ingredient.ingredient_id, ingredient.ingredient_name
FROM ingredient
LEFT JOIN contains ON ingredient.ingredient_id != contains.ingredient_id
LEFT JOIN ice_cream ON ice_cream.ice_cream_id != contains.ice_cream_id
ORDER BY ingredient.ingredient_id;

Output columns should be as follows:

ingredient_id  ingredient_name

----------------------------

6              Dark chocolate

and here are the tables:

CREATE TABLE manufacturer (
    manufacturer_id INT,
    manufacturer_name VARCHAR(30) NOT NULL,
    country VARCHAR(30) NOT NULL,
    PRIMARY KEY (manufacturer_id),
    UNIQUE (manufacturer_name)
);

-- jäätelöitä
-- ice creams

CREATE TABLE ice_cream (
    ice_cream_id INT,
    ice_cream_name VARCHAR(30) NOT NULL,
    manufacturer_id INT NOT NULL,
    manufacturing_cost NUMERIC(4,2),
    PRIMARY KEY (ice_cream_id),
    UNIQUE (ice_cream_name),
    FOREIGN KEY (manufacturer_id) REFERENCES manufacturer
);

-- aineksia
-- ingredients
-- plant_based arvo 0 merkitse ei ja arvo 1 merkitsee kyllä
-- plant_based value 0 means no and value 1 means yes 

CREATE TABLE ingredient (
    ingredient_id INT,
    ingredient_name VARCHAR(30) NOT NULL,
    kcal INT,
    protein NUMERIC(3,1),
    plant_based INT,
    PRIMARY KEY (ingredient_id),
    UNIQUE (ingredient_name)
);

-- jäätelöt sisältävät aineksia
-- ice creams contain ingredients

CREATE TABLE contains(
    ice_cream_id INT NOT NULL,
    ingredient_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (ice_cream_id, ingredient_id),
    FOREIGN KEY (ice_cream_id) REFERENCES ice_cream,
    FOREIGN KEY (ingredient_id) REFERENCES ingredient
);




--Ice cream manufacturers
INSERT INTO manufacturer VALUES (
    1, 'Jen & Berry', 'Canada'
);

INSERT INTO manufacturer VALUES (
    2, '4 Friends', 'Finland'
);

INSERT INTO manufacturer VALUES (
    3, 'Gelatron', 'Italy'
);

--Ice cream

INSERT INTO ice_cream VALUES (
    1, 'Plain Vanilla', 1, 1.00
);

INSERT INTO ice_cream VALUES (
    2, 'Vegan Vanilla', 2, 0.89
);

INSERT INTO ice_cream VALUES (
    3, 'Super Strawberry', 2, 1.44
);

INSERT INTO ice_cream VALUES (
    4, 'Very plain', 2, 1.20
);


--Ingredients
INSERT INTO ingredient VALUES (
    1, 'Cream', 400, 3, 0
);

INSERT INTO ingredient VALUES (
    2, 'Coconut cream', 230, 2.3, 1
);

INSERT INTO ingredient VALUES (
    3, 'Sugar', 387, 0, 1
);

INSERT INTO ingredient VALUES (
    4, 'Vanilla extract', 12, 0, 1
);


INSERT INTO ingredient VALUES (
    5, 'Strawberry', 33, 0.7, 1
);

INSERT INTO ingredient VALUES (
    6, 'Dark chocolate', 535, 8, 1
);


--Contains

INSERT INTO contains VALUES ( 
    1, 1, 70
);
INSERT INTO contains VALUES ( 
    1, 3, 27
);
INSERT INTO contains VALUES ( 
    1, 4, 3
);


INSERT INTO contains VALUES ( 
    2, 2, 74
);
INSERT INTO contains VALUES ( 
    2, 3, 21
);
INSERT INTO contains VALUES ( 
    2, 4, 5
);

INSERT INTO contains VALUES ( 
    3, 1, 60
);
INSERT INTO contains VALUES ( 
    3, 3, 10
);
INSERT INTO contains VALUES ( 
    3, 5, 30
);


INSERT INTO contains VALUES ( 
    4, 2, 95
);
INSERT INTO contains VALUES ( 
    4, 4, 5
);

CodePudding user response：

What you can do is joining the contains table with the ingredient table using a LEFT JOIN and filter all values that have NULL in the contains.ice_cream_id field, something like this:

SELECT 
    ingredient.ingredient_id,
    ingredient.ingredient_name,
    contains.ice_cream_id
FROM 
    ingredient
LEFT JOIN 
    contains
ON 
    ingredient.ingredient_id = contains.ingredient_id
WHERE 
    contains.ice_cream_id IS NULL

CodePudding user response：

It appears you are asking to find where somethig does not exist, so it would make sense to express that using not exists

So you just need to find the ingredients that don't exist in the list of contents:

select ingredient_id, ingredient_name
from ingredient i
where not exists (
  select * from contains c 
  where c.ingredient_id = i.ingredient_id
);

Demo Fiddle