[thelist] An SQL query about null and =

[Luther, Ron]

john at johnallsopp.co.uk noted:

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

Hi John,

Yup, it came thru. I saw it yesterday.

>>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 [in?]correct results.

I think Joshua suggested looking at the 'Coalesce' command. (I think 
that's T-SQL for SQL Server.) I was going to chime in and suggest 
'NVL(thing1,thing2)' as the corresponding Oracle syntax ... but I got 
busy.


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

{Just real quick - the reason you get no results here is because 
a.field1 <> a.field2 ... associative law and stuff like that.}

Looking at this a little more closely today, I think I may have misread 
the question.

I was thinking along the lines of:

SELECT [typed out list of fields (Rudy isn't here to yell about 'Select * 
being evil' these days.)] FROM a, b WHERE NVL(a.field2, a.field1) = b.field1 
and (b.field2 like 'cats' or b.field2 like 'dogs')

[However, I don't think that will quite do it for you ... Suppose a.field2 
contains a non-null value of "llamas" and a.field1 contains a non-null 
value of "dogs" ... if table b doesn't contain any records for "llamas", 
then you shouldn't get a record back - even if table b contains a record 
for "dogs".]

So I'm thinking that maybe a union would get you all matching records:

SELECT [typed out list of fields] FROM a, b WHERE a.field1 = b.field1 and 
(b.field2 like 'cats' or b.field2 like 'dogs')

UNION 

SELECT [same typed out list of fields in the same order] FROM a, b WHERE 
a.field2 = b.field1 and (b.field2 like 'cats' or b.field2 like 'dogs')

However, that will probably bring back duplicate records -- for those 
cases where both a.field1 and a.field2 match records in table b.

Because I don't remember if "Select Distinct" plays nicely with unions 
(and because I usually end up mangling subqueries) ... I'd suggest writing 
the union out to a temp table and then pulling a 'Select Distinct' out of 
the temp table to eliminate the duplicate records.


HTH,

RonL.