SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What is the SQL for this?

    All I want to know is if an order contains any product that belongs to a specific category. categoryID is not on the orderdetails table, however.

    So I start with my query on the order detail table. Something like select orderID, productID from orderdetails where orderID = xxxx. This will return multiple results of productIDs. I then want to take each productID and look up the products table to determine if each product belongs to a specific category. If I get any match or not is all I want to know.

    What SQL statements might achieve this result? Can a single query statement get me the result?
    renkai.com

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,496
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What you need is an INNER JOIN:
    Code:
    SELECT
        od.orderID
      , od.productID 
    FROM orderdetails AS od
    INNER JOIN products AS p
    ON od.productID = p.ID
    WHERE od.orderID = xxxx
    AND p.categoryID = yyy

  3. #3
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    wow... I understand INNER JOIN now!! Thank you!
    renkai.com

  4. #4
    SitePoint Zealot
    Join Date
    May 2008
    Posts
    112
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well this is getting more complex again. Actually what I have to start with is a list of each order from the order table, but problem is the order table does not contain information about the product at all. Instead, the orderdetails table has that. So I have to incorporate the inner join statement into a select statement on the order table. Make sense?

    What I'm starting with is this:

    SELECT ID, OrderDate, ShipDate
    FROM Orders
    WHERE BillToID= xxx

    Is there a way to incorporate the other query that looks up the orderdetails table? Each row I need a field that says yes or no - a product from that order either does or does not belong to a category.
    renkai.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Renkai View Post
    Is there a way to incorporate the other query that looks up the orderdetails table?
    yes

    start with FROM Orders as you have there

    then add INNER JOIN OrderDetails ON ...

    then add INNER JOIN Products ON ...

    then put the columns you want into the SELECT clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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