Selecting where join condition not equal true

Superfly1611 · #1 (**permalink**) 04-14-06, 11:13

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

r937 · #2 (**permalink**) 04-14-06, 11:22

your requirement:

Quote:

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

Superfly1611 · #3 (**permalink**) 04-14-06, 12:06

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

r937 · #4 (**permalink**) 04-14-06, 14:41

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

Superfly1611 · #5 (**permalink**) 04-15-06, 08:27

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

r937 · #6 (**permalink**) 04-15-06, 09:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech