Martin Martin - 2 months ago 8
SQL Question

SQL Query problems exist

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

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);