0

I'm new to SQL and having some trouble with this one, but I feel like the answer is fairly obvious and I'm missing something. Here is the query I've tried:

SELECT r.restaurantName, r.city
FROM Restaurant r, MenuItem m
WHERE m.itemNo = 44;

These are the tables I'm using:

CREATE TABLE Restaurant(restaurantID Integer Primary Key, restaurantName Char(15) NOT NULL, type Char(15) NOT NULL, city char(15) NOT NULL);

CREATE TABLE Dish(dishNo Integer Primary Key, dishName Char(15) NOT NULL, type ENUM ('ap', 'en', 'ds'));

CREATE TABLE MenuItem(itemNo Integer Primary Key, restaurantNo int, dishNo int, price DECIMAL(4,2) CHECK(price IN(price>=5 and price<=50)), FOREIGN KEY(restaurantNo) REFERENCES Restaurant(restaurantID) ON DELETE NO ACTION, FOREIGN KEY(dishNo) REFERENCES Dish(dishNo) ON DELETE SET NULL);

CREATE TABLE FoodOrder(orderNo Integer Primary Key, itemNo int, dateof date CHECK(dateof IN(dateof>='2017-01-01')), timeof time, FOREIGN KEY(itemNo) REFERENCES MenuItem(itemNo) ON DELETE CASCADE);

Inserted data:

INSERT INTO Dish Values(13, 'Spring Rolls', 'ap');
INSERT INTO Dish Values(15, 'Pad Thai', 'en'); 
INSERT INTO Dish Values(16, 'Pot Stickers', 'ap');    
INSERT INTO Dish Values(22, 'Masaman Curry', 'en');   
INSERT INTO Dish Values(10, 'Custard', 'ds');   
INSERT INTO Dish Values(12, 'Garlic Bread', 'ap');    
INSERT INTO Dish Values(44, 'Salad', 'ap');    
INSERT INTO Dish Values(07, 'Cheese Pizza', 'en');   
INSERT INTO Dish Values(19, 'Pepperoni Pizza', 'en');    
INSERT INTO Dish Values(77, 'Veggie Supreme Pizza', 'en');

INSERT INTO MenuItem Values(0, 0, 13, 8.00);
INSERT INTO MenuItem Values(1, 0, 16, 9.00);
INSERT INTO MenuItem Values(2, 0, 44, 10.00);
INSERT INTO MenuItem Values(3, 0, 15, 19.00);
INSERT INTO MenuItem Values(4, 0, 22, 19.00);
INSERT INTO MenuItem Values(5, 3, 44, 6.25);
INSERT INTO MenuItem Values(6, 3, 12, 5.50);
INSERT INTO MenuItem Values(7, 3, 07, 12.50);
INSERT INTO MenuItem Values(8, 3, 19, 13.50);
INSERT INTO MenuItem Values(9, 5, 13, 6.00);
INSERT INTO MenuItem Values(10, 5, 15, 15.00);
INSERT INTO MenuItem Values(11, 5, 22, 14.00);

Example of one of the joins I have tried:

SELECT DISTINCT Restaurant.restaurantName, Restaurant.city
FROM Restaurant
INNER JOIN MenuItem
ON MenuItem.restaurantNo = Restaurant.restaurantID AND MenuItem.itemNo = 44;
1
  • 4
    BTW MySQL doesn't validate check constraints but if it did this would be a problem CHECK(price IN(price>=5 and price<=50)) Mar 4, 2017 at 20:29

1 Answer 1

3
SELECT Restaurant.restaurantName,
       Restaurant.city
FROM   Restaurant,
       MenuItem,
       Dish
WHERE  Dish.dishName = 'Salad'
       AND Dish.dishNo = MenuItem.dishNo
       AND MenuItem.restaurantNo = Restaurant.restaurantNo
ORDER  BY Restaurant.city,
          Restaurant.restaurantName 

Not the answer you're looking for? Browse other questions tagged or ask your own question.