[thelist] An SQL query about null and =

john at johnallsopp.co.uk john at johnallsopp.co.uk
Thu Jan 20 03:36:05 CST 2005


Hi

(I tried posting this yesterday but didn't see it appear on the list
or any replies, apologies if you see a double post)

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')

There are other tables, and other fields in the brackets, so if I
remove the table b that structure's still there.

I looked up null comparisons and tried <=> and (a.field2 = b.field1 or
a.field2 IS NULL) but the former returned nothing, and the latter
returned just one record rather than several.

Any clues anyone?

Cheers
J




More information about the thelist mailing list