SQL Query problems exist

Question

I'm having a lot of troubles with the last query I need and I think it's a level out of my league so any help is appreciated.

The tables:

CREATE TABLE Recipe
(
     nrecipe     integer, 
     name         varchar(255),
     primary key (nrecipe)
);

CREATE TABLE Food
(
    designation  varchar(255)   unique,
    quantity     integer,
    primary key (designation)
);

CREATE TABLE Contains
(
     nrecipe      integer,
     designation  varchar(255),
     quantity     integer,
     primary key (nrecipe, designation),
     foreign key (nrecipe) references Recepie (nrecipe),
     foreign key (designation) references Food (designation)
);

Quantity in Food table is the quantity stored in warehouse.

Quantity in Contains is the amount needed of a food element to use in recipe.

Quantity in Food table and Contains differ from each other.

The query:

I want to know the names of ALL recipes that are possible to be done with the food stored in warehouse.

It requires that the quantity of every element of food in warehouse is bigger than the quantity needed for the recipe.

EDIT: also, it shouldn't show a recipe's name if there is nothing referring to it on Contains table.

To make it easier to understand, I'll give some data:

INSERT INTO Recipe  VALUES ('01', 'Steak with potatos and water');
INSERT INTO Recipe  VALUES ('02', 'Rice and ice tea');
INSERT INTO Recipe  VALUES ('03', 'Potatos and shrimp');
INSERT INTO Recipe  VALUES ('04', 'Water');
INSERT INTO Recipe  VALUES ('05', 'Steak with rice');
INSERT INTO Recipe  VALUES ('06', 'Steak with spaguetti');
INSERT INTO Recipe  VALUES ('07', 'Potatos with rice');

INSERT INTO Food    VALUES ('Water', 5);
INSERT INTO Food    VALUES ('Ice tea', 10);
INSERT INTO Food    VALUES ('Steak', 30);
INSERT INTO Food    VALUES ('Potatos', 20);
INSERT INTO Food    VALUES ('Rice', 50);
INSERT INTO Food    VALUES ('Shrimp', 5);
INSERT INTO Food    VALUES ('Spaguetti', 5);

INSERT INTO Contains    VALUES ('01', 'Steak', 1);
INSERT INTO Contains    VALUES ('01', 'Potatos', 15);
INSERT INTO Contains    VALUES ('01', 'Water', 10);
INSERT INTO Contains    VALUES ('02', 'Rice', 5);
INSERT INTO Contains    VALUES ('02', 'Ice tea', 8);
INSERT INTO Contains    VALUES ('03', 'Potatos', 1);
INSERT INTO Contains    VALUES ('03', 'Shrimp', 10);
INSERT INTO Contains    VALUES ('04', 'Water', 20);
INSERT INTO Contains    VALUES ('05', 'Steak', 1);
INSERT INTO Contains    VALUES ('05', 'Rice', 20);
INSERT INTO Contains    VALUES ('06', 'Steak', 1);
INSERT INTO Contains    VALUES ('06', 'Spaguetti', 10);

The outcome expected from the query is:

Rice and ice tea
Steak with rice

Since it's the only two recipes with enough quantity in warehouse.

EDIT: potatoes with rice shouldn't appear as it is a recipe but isn't in contains list

Thanks for input and time. Any help is welcome :)

Answer 1

I'd use >= ALL operator :

SELECT name
  FROM Recipe R
 WHERE 0 >= ALL (SELECT C.quantity - F.quantity
                   FROM Food F
             INNER JOIN Contains C
                  USING (designation)
                  WHERE C.nrecipe = R.nrecipe);

The correct spelling is recipe, and you used different names for some columns (recepie, nrecipe, nrecepie) so I changed it. Note that instead of using a varchar primary key, you should use a numeric one.

Edit:

SELECT name
  FROM Recipe R
 WHERE 0 >= ALL (SELECT C.quantity - F.quantity
                   FROM Food F
             INNER JOIN Contains C
                  USING (designation)
                  WHERE C.nrecipe = R.nrecipe)
   AND EXISTS(SELECT NULL
                FROM Contains C
               WHERE C.nrecipe = R.nrecipe);

Answer 2

This is in SQL Server because that is what I have:

select
    r.name
from
    Recepie r
where
    not exists 
    (
        select 1
        from
            [Contains] c
        where
            c.nrecipe = r.nrecepie and
            not exists 
            (
                select 1
                from
                    Food f
                where
                    f.designation = c.designation and
                    f.quantity >= c.quantity
            )
    )

Which in plain language is "Get me all recipes where there are no ingredients of insufficient quantity"