Results 1 to 8 of 8
  1. #1
    Join Date
    May 2005
    Posts
    3

    Unanswered: Need help with a query

    This is my first post, hope it makes sense.

    I have two tables that are in a one-to-many relationship: OrderSummary and OrderDetail (see below). I need a query that can filter the OrderSummary table and show orders that do not include a particular item e.g. show all orders that do not have ItemNo 012346, in which case it would only return OrderNo 2 from the OrderSummary table.

    HTML Code:
    OrderSummary
    
    OrderNo		CustNo		CustName
    ========	======		=========
    1		COM001		Company A
    2		COM002		Company B
    3		COM003		Company C
    
    
    
    OrderDetail
    
    OrderNo		ItemNo		ItemDesc
    ========	======		=========
    1		012345		sample1
    1		012346		sample2
    1		012100		sample3
    2		012416		sample4
    2		012417		sample5
    3		012346		sample2
    3		012999		sample6


    Apologies if this is something really simple but I can't get my head around it!

    Thanks,
    Peter.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Peter - welcome to the forum

    Possible SQL:
    Code:
    SELECT OS.OrderNo, CustNo, CustName, ItemNo, ItemDesc
    FROM OrderSummary AS OS INNER JOIN OrderDetail AS OD ON
    OD.OrderNo = OS.OrderNo
    WHERE OS.OrderNo IN (SELECT OrderNo FROM OrderSummary WHERE ItemNo <> 012346)
    BTW - I'm a tad nervous that your have CustName in the order summary table - shouldn't that be in a customer table?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Isn't this just a variation of the Find Unmatched Query ????
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    Isn't this just a variation of the Find Unmatched Query ????
    That was my first thought. But...
    Code:
    SELECT OS.OrderNo, CustNo, CustName, ItemNo, ItemDesc
    FROM OrderSummary AS OS LEFT OUTER JOIN OrderDetail AS OD ON
    OD.OrderNo = OS.OrderNo
    WHERE OD.OrderNo <> 012346
    would return all OrderNos (all the orders have at least one order that isn't 012346).
    You could have:
    Code:
    SELECT OS.OrderNo, CustNo, CustName, ItemNo, ItemDesc
    FROM OrderSummary AS OS LEFT OUTER JOIN 
    (SELECT * FROM OrderDetail WHERE OrderNo = 0123456) AS OD ON
    OD.OrderNo = OS.OrderNo
    WHERE OD.OrderNo IS NULL
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT OS.OrderNo, CustNo, CustName, ItemNo, ItemDesc
    FROM OrderSummary AS OS INNER JOIN OrderDetail AS OD ON OD.OrderNo = OS.OrderNo WHERE OS.OrderNo IN (SELECT OrderNo FROM OrderSummary WHERE ItemNo <> 012346)
    Quote Originally Posted by M Owen
    Isn't this just a variation of the Find Unmatched Query ????
    Oops - yep - you are right Was +ve where I should have been -ve. And over complicated it. And got the wrong table in corrolated subq... lol

    Code:
    SELECT OrderNo, CustNo, CustName FROM OrderSummary
    WHERE OrderNo NOT IN (SELECT OrderNo FROM OrderDetail WHERE ItemNo = 012346)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    May 2005
    Posts
    3
    That last one worked a treat, Thanks!

    Just to be a bit more of a pain, can you tell me how to modify this query so that I can enter up to 5 different criteria (ItemNo's), i.e. show orders that don't include any of the items?

    BTW - I'm a tad nervous that your have CustName in the order summary table - shouldn't that be in a customer table?
    P.S. My mistake on the CustName column, its not like that on the actual database I'm working on. For some reason I decided to put it in the example? D'oh!

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by peterthanson
    Just to be a bit more of a pain, can you tell me how to modify this query so that I can enter up to 5 different criteria (ItemNo's), i.e. show orders that don't include any of the items?
    Easy enough - check out the IN predicate as one option.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    May 2005
    Posts
    3
    Spot on, thanks again for your help!

Posting Permissions

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