mySQL query help: left join?

jalperin

I have a db with 2 tables:

orders-- which holds information about orders; and
status -- which holds messages about the status of the orders.
There can be from 0 to several different status records for each order.

A simplified version of the database design is:
orders.id (primary key)
orders.name

status.id (primary key)
status.orders_id (used to link to issue)
status.description

I'd like to design a query which will return all orders which do not have a status.description = "hold". Note that this means that I want orders which have no status record at all, as well as those which do have status records, but none where the description field = "hold".

I've tried this query:

SELECT * FROM orders LEFT JOIN status ON status.orders_id = orders.id
WHERE status.description IS NULL OR status.description <> "hold"

but the query returns incorrect results in the following situation:

orders.id   orders.name
=====    ============
1              order1
2              order2

status.id  status.orders_id  status.description
======  ============ =============
1               1                          hold
2               1                          credit check

I want a query which will return order2 only, because there is a status.description record = "hold" for order1, but my query returns both order1 and order2.

Any ideas? Thanks.

sneakyimp

sounds to me like that query should work...maybe try it with != instead of <> ?

SELECT o., s. FROM orders o LEFT JOIN status s ON s.orders_id = o.id
WHERE s.description IS NULL OR s.description != "hold"

Lars_Berg

SELECT * 
  FROM orders 
 where not exists
  (select *
       from status
    where status.orders_id = orders.id
        and status.description = 'hold')

jalperin

Thanks Lars. I'm using mySQL 4.0 so I can't use subqueries. Any other thoughts?

Lars_Berg

SELECT o.*, s.* 
   FROM orders o LEFT JOIN status s 
  ON s.orders_id = o.id
   and s.description = 'hold'
 WHERE s.description IS NULL

jalperin

Thanks; works great! I hadn't ever JOINed on more than one column, but that did the trick.