asked on

Why does this SQL bring back null?

I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window

SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

This brings back

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0 AND SiteCountryCode = 'USA')
Or (IsNull([SiteAddress], 'NULL') <> 'Null')

Open in new window

This seems to be correct

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

FROM table
WHERE (Deleted =0 AND SiteCountryCode = 'USA')
and (SiteAddress IS not NULL)
---

SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ste5an

This should work:

SELECT DISTINCT SiteAddress ,
                sitecity ,
                SiteState ,
                SiteZip ,
                SiteCountryCode
FROM   tableName
WHERE  Deleted = 0
       AND SiteCountryCode = 'USA'
       OR SiteAddress IS NOT NULL;

Open in new window

Camillia

ASKER

Has to be "AND" or I'd still get null

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

FROM table
WHERE (Deleted =0 AND SiteCountryCode = 'USA')
and (SiteAddress IS not NULL)

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Camillia

ASKER

Got it. Thanks, guys.