Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2010
    Posts
    3

    Unanswered: Help with an sql query...

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

    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!

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by Gungrave12 View Post
    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?

  4. #4
    Join Date
    Feb 2010
    Posts
    2
    Find the cust num of each order where all the items are of food type 'soup'

    PLEASE HELP ME OUT WITH THIS ONE
    THANKS

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rounak11 View Post
    PLEASE HELP ME OUT WITH THIS ONE
    stop shouting, and we might give you some hints
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2010
    Posts
    2
    the question which i posted is entirelly different from the above only the table structure is similar.

    Thanks

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rounak11 View Post
    Thanks
    you're welcome

    what have you tried?

    please realize we don't just hand out answers to homework questions on this site
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •