Help with an sql query...

Gungrave12 · #1 (**permalink**) 02-20-10, 20:33

Hey guys, could you point me in the right direction for writing this SQL query?
This is the problem as given to me (a HW problem):

Quote:

CREATE TABLE menu (food_name VARCHAR(20) not NULL,
food_type VARCHAR(20) not NULL,
price decimal(4,2) unsigned NOT NULL,
PRIMARY KEY (food_name)
);
CREATE TABLE customer (cust_num INT(5) not NULL,
phone INT(10) not NULL,
address VARCHAR(40) not NULL,
PRIMARY KEY(cust_num)
);
CREATE TABLE orders (cust_num INT(5) not NULL,
food_name VARCHAR(20) not NULL,
quantity INT(3) unsigned NOT NULL,
PRIMARY KEY (cust_num, food_name),
FOREIGN KEY (food_name) REFERENCES menu,
FOREIGN KEY (cust_num) REFERENCES customer,
);

11. With the tables above, find the phone number of each customer who ordered some food with food type 'soup' and ordered
three hamburgers.

So, this is what I tried (after hours of trying

):

Code:

SELECT orders.cust_num, orders.food_name, menu.food_type, quantity, phone
FROM (
orders
INNER JOIN menu ON orders.food_name = menu.food_name
)
INNER JOIN customer ON customer.cust_num = orders.cust_num
WHERE orders.food_name = some(
SELECT food_name
FROM menu
WHERE food_type =  'soup'
)
OR (
orders.food_name =  'hamburger'
AND orders.quantity =3
)

But unfortunately, this gets me the phone numbers of anyone who ordered soup or 3 hamburgers. I can't quite figure out how to get only the #'s of those who have done both. I'd greatly appreciate a push in the right direction!

n_i · #2 (**permalink**) 02-24-10, 17:34

Your query can be explained in plain English as "select all orders of 3 hamburgers or those where food was of type soup", so the result you get corresponds to what you've asked.

What you need is more like "select customers for whom exist orders of some soup AND exist orders of 3 hamburgers"; as you can see this is almost exactly the text of problem 11 in your assignment. Note that "exists" is a valid SQL predicate.

futurity · #3 (**permalink**) 02-25-10, 12:07

Quote:

Originally Posted by Gungrave12

find the phone number of each customer who ordered some food with food type 'soup' and ordered three hamburgers.

So you're interested in two orders: an order for soup, and another order for hamburgers. Furthermore, these two orders must have the same customer.

Now your query only involves one order.

Does that help?