Rails - Aug 2006 - help:sql search for NULL

Hi,

I have two tables with identical fields(assignments, and matches). 
However, only one of them accept NULL values for its fields. I want to 
be able to find out all the  matches for any assignment. The search 
should retrun the match if the corresponding filed is NULL as well.
I wrote down the following code but it doesn''t return anything. I
wonder
what  I am doing wrong. Any help is greately appreciated.

 def self.match_for_assignment(assignment)
    self.find(:all,
              :conditions => [''matches.member_id IN (?, NULL) AND 
matches.study_id   IN (?, NULL) AND matches.tag_id IN (?, NULL)'',
                              assignment.member.id, assignment.study.id, 
assignment.tag.id])

  end

-- 
Posted via http://www.ruby-forum.com/.

When checking for NULL, you have to use IS NULL or IS NOT NULL.  NULL 
cannot be evaluated like other values--because it is not a value.  So, 
your condition string should look more like this:

''(matches.member_id = ? OR matches.member_id IS NULL)
 AND (matches.study_id = ? OR matches.study_id IS NULL)
 AND (matches.tag_id = ? OR matches.tag_id IS NULL)''

CS

Andy Black wrote:
> Hi,
> 
> I have two tables with identical fields(assignments, and matches). 
> However, only one of them accept NULL values for its fields. I want to 
> be able to find out all the  matches for any assignment. The search 
> should retrun the match if the corresponding filed is NULL as well.
> I wrote down the following code but it doesn''t return anything. I
wonder
> what  I am doing wrong. Any help is greately appreciated.
> 
>  def self.match_for_assignment(assignment)
>     self.find(:all,
>               :conditions => [''matches.member_id IN (?, NULL)
AND
> matches.study_id   IN (?, NULL) AND matches.tag_id IN (?, NULL)'',
>                               assignment.member.id, assignment.study.id, 
> assignment.tag.id])
> 
>   end

-- 
Posted via http://www.ruby-forum.com/.