Select by joining NULL value with NULL value? 1

[bmacbmac]

Hello. In SQL 2000...

I am running a select statement..

I am Joining:
DB2 tbldocumentinfo to DB1
DB2 tblland to DB2 tbldocumentinfo
DB2 tblacreage to DB2 tblland

select col002, col029, col032, col33, srange, stownship,  sdocumentno, DB2..tblland.szcomment 
from DB1
left join DB2..tbldocumentinfo 
     on col002 = DB2..tbldocumentinfo.sdocumentno
left join DB2..tblland 
     on DB2..tbldocumentinfo.irecordid = DB2..tblland.irecordid
          and ltrim (DB2..tblland.stownship) = col032 
          and ltrim (DB2..tblland.srange) = col033 
          and (DB2..tblland.szcomment) = col029
left join DB2..tblacreage 
     on DB2..tblacreage.ilandid = DB2..tblland.ilandid

This script works, however, I'm wondering if there is a way to use the line:
and (DB2..tblland.szcomment) = col029
to join where the value in DB2..tblland.szcomment is NULL and COL029 is NULL?

When I run the script as is I get about 3000 rows. If I take that line out I get about 11,000 rows becuase of the NULL values.

I'm hoping to join that field based on NULL as well so all 11,000 rows come up joined together properly.

Thanks for looking!

 

[bborissov]

You could use the ISNULL() function and set both to an empty value depending of the type of the field type, something like:

and ISNULL(DB2..tblland.szcomment,'') = ISNULL(col029,'')

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Do you have other JOIN criteria? Otherwise which NULL you will be joining with which other NULL?

PluralSight Learning Library

 

[bmacbmac]

Thanks Borislav. That worked perfectly!

 

[markros]

How did it work, in other words, did it create a CROSS JOIN?

PluralSight Learning Library