[thelist] An SQl query about null and =

[Joshua Olson]

> -----Original Message-----
> From: john at johnallsopp.co.uk
> Sent: Wednesday, January 19, 2005 5:00 AM
> 
> I have a rather long SQL statement where a main table holds two
> category fields relating to the same lookup table. The second category
> field may well be null.
> 
> If I include the second category field in my selection, I end up with
> no results, but if I don't, I get correct results.
> 
> I think it's to do with the field being null, but it may be something
> I don't understand about the fact that both fields connect to the same
> table.
> 
> The SQL is like this:
> 
> SELECT * FROM a, b WHERE a.field1 = b.field1 and a.field2=b.field1 and
> (b.field2 like 'cats' or b.field2 like 'dogs')

John,

The equality operator, =, doesn't necessarily work with a NULL value.
Generally, you'd use IS NULL to compare a value to NULL.  Couple things may
help you out here...

1.  Change the syntax from "FROM a, b" to using INNER JOIN and OUTER JOIN
appropriately.  The JOIN syntax has much greater flexibility in the way
tables are brought together.

2.  Use Coalesce (or whatever similar function your DB supports) to change
NULL values into something compatible with the equality operator for use in
the query... example, using your original query:

SELECT * FROM a, b WHERE a.field1 = b.field1 and Coalesce(a.field2, 0) =
Coalesce(b.field1, 0) and
(b.field2 like 'cats' or b.field2 like 'dogs')

Hoping this helps,

<><><><><><><><><><>
Joshua Olson
Web Application Engineer
WAE Tech Inc.
http://www.waetech.com/service_areas/
706.210.0168