Join statement, but need to only get data based on WHERE table column is empty.

Question

I have this query statement and want to only get records that has a certain column empty (volunteers_2009.venue_id)

Table is volunteers_2009, column I am looking to see if it is empty: venue_id

Here is the current query:

SELECT volunteers_2009.id, volunteers_2009.comments, 
   volunteers_2009.choice1, volunteers_2009.choice2, volunteers_2009.choice3, 
   volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, 
   venues.venue_name 
FROM volunteers_2009 AS volunteers_2009 
LEFT OUTER JOIN venues ON (volunteers_2009.venue_id = venues.id) 
ORDER by $order $sort

I am trying to do this:

SELECT volunteers_2009.id, volunteers_2009.comments, 
   volunteers_2009.choice1, volunteers_2009.choice2, volunteers_2009.choice3, 
   volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, 
   venues.venue_name 
FROM volunteers_2009 AS volunteers_2009 
LEFT OUTER JOIN venues ON (volunteers_2009.venue_id = venues.id) 
ORDER by $order $sort 
WHERE volunteers_2009.venue_id == ''

How would I only list records that have an empty column (venue_id) within the table (volunteers_2009)?

Answer 1

By empty do you mean null? If the venue_id field can contain nulls then you can compare using the is operator like this:

WHERE volunteers_2009.venue_id is null

Answer 2

The WHERE clause is out of order in your 2nd query. It must go before the ORDER BY clause.

Also, I don't imagine you have any venues with an empty id. Perhaps what you really want is this:

SELECT volunteers_2009.id, volunteers_2009.comments, 
    volunteers_2009.choice1, volunteers_2009.choice2, volunteers_2009.choice3, 
    volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, 
    venues.venue_name 
FROM volunteers_2009 
LEFT JOIN venues ON venue_id = venues.id
WHERE venues.id IS NULL
ORDER BY $order $sort

That will bring back only volunteers_2009 records that don't match any venues.

Or this:

SELECT volunteers_2009.id, volunteers_2009.comments, 
    volunteers_2009.choice1, volunteers_2009.choice2, volunteers_2009.choice3, 
    volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, 
    venues.venue_name 
FROM venues
LEFT JOIN volunteers_2009 ON volunteers_2009.venue_id = venues.id
WHERE volunteers_2009.venue_id IS NULL
ORDER BY $order $sort

to find venues with no volunteers.