Results 1 to 5 of 5

Thread: Query

  1. #1
    Join Date
    Oct 2004
    Posts
    60

    Unanswered: Query

    Hi

    I have table called Order_Items which stores all the Ordered Items.

    Table Columns : Order_ID, Item_Id, Qty, Price

    I want to retrive all the OrderIDs which has all the items of a particular order.

    Eg. I have a Order say 1011 whcih has Item_ID 1,2,3,4. So now i want to list the order_IDs which has Items 1,2,3,4 (All of the 4 items). Orders which has all the Items of Order_ID 1011.

    Any help will be highly appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT Order_ID
      FROM Order_Items 
     WHERE Item_ID IN ( 1,2,3,4 )
    GROUP
        BY Order_ID
    HAVING COUNT(*) = 4
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2004
    Posts
    60
    SELECT Order_ID
    FROM Order_Items A
    JOIN (SELECT Item_ID FROM Order_Items WHERE Order_ID = 1011) B
    ON A.Item_ID = B.Item_ID
    GROUP BY Order_ID
    HAVING COUNT(*) = (SELECT COUNT(*) FROM Order_Items WHERE Order_ID = 1011)

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post SQL without comment is useless when we don't have your tables or data to run it.
    Code:
    SELECT order_id
    FROM   order_items a
           join (SELECT item_id
                 FROM   order_items
                 WHERE  order_id = 1011) b
             ON a.item_id = b.item_id
    GROUP  BY order_id
    HAVING COUNT(*) = (SELECT COUNT(*)
                       FROM   order_items
                       WHERE  order_id = 1011)
    Is your problem solved or not?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2004
    Posts
    60
    Yes, Thanks all for your time.

    anacedent
    I will keep your suggestion in my mind for my future post.

    Thanks

Posting Permissions

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