Help with an sql query...

[gungrave12]

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


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 :eek:):

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!

I think what I have to do is first get a list of customer numbers which have bought soup, then get a list of customer numbers which have bought three hamburgers, and then inner join the two. But i cant seem to figure out how to write this in SQL.

I'm in desperate need of help!

[blondie_69]

Ooops

[blondie_69]

Ok this seems to work. The logic is as follows: first get all the customers that have purchased food of type soup and among them find those that have purchased food of type 'hamburger' with quantity 3. So you have the cust_num and then you look it up in the customer table to get the phone number.

Here though, my assumption is that 'hamburger' is a food_type and not food_name.

Please let me know if that works for you


SELECT customer.phone
FROM (SELECT cust_num
FROM orders
WHERE food_name IN (SELECT food_name
FROM menu
WHERE menu.food_type='soup')
) as temp, customer
WHERE temp.cust_num IN (SELECT cust_num
FROM orders
WHERE quantity = 3
AND food_name IN (SELECT food_name
FROM menu
WHERE food_type='hamburger')

AND temp.cust_num = customer.cust_num
)