Help:sql search for NULL

Rails
1

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

2

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 B. 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