Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40

    Unanswered: Selecting where join condition not equal true

    Hey all,

    I'm trying to write some SQL that queries a mySQL database against two relational tables.
    Consider two classic database tables: tblProducts and tblOrderLines

    I would like to query the database for all products in the tblProducts table that are NOT on a particular order in the tblOrderLines table in a single query.

    This is for a web application so i want as few round trips to the database as possible. I'm quite capable of doing it in multiple queries and a bit of business logic in ASP but i'd rather keep it clean in a single database query.

    I had planned on showing you my workings so far but to be quite honest i've not got close yet

    Can anyone shed some light?

    Many Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your requirement:
    all products in the tblProducts table that are NOT on a particular order in the tblOrderLines table
    the necessary sql:
    Code:
    select P.id
         , P.descr
         , P.price
      from tblProducts as P
    left outer
      join tblOrderLines as OL
        on OL.product_id = P.id
       and OL.order_id = 937
     where OL.product_id is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    r937 - thank you so much.
    I don't know why I cant get my head round SQL, it just doesn't logically fit in my brain.
    If you happen to find yourself with a spare 5 mins and feel like taking pity on this n00b an explanation of that query would be greatly appreciated.

    I always thought I understood the different join types but it's become obvious... i don't

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i know a lot of SQL tricks, but this isn't one of them

    it's a classic "find rows in A that have no match in B"

    it uses a LEFT OUTER JOIN with a test for NULL in the right table

    every basic SQL tutorial should be able to explain this one, under LEFT OUTER JOIN -- if your favourite SQL tutorial doesn't, it's deficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2004
    Location
    UK
    Posts
    40
    Well from reading up on a LEFT OUTER JOIN i have to conclude that i was right in my understanding of them before, i just never knew that you could put in other join conditions other than linking two fields together (and OL.order_id = 937 for example)

    I've never used outer joins in that way before, I just immediately set about this task assuming that somwhere would be some form of OL.Order_id NOT LIKE 937 involved, never thought about using a test for null instead.

    Thanks

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, it's a bit weird at first, but when you understand how NULLs are used for the columns from unmatched rows, it makes sense that testing for nulls would give you only the unmatched rows
    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
  •