Select rows where address is not null

[richardrekos]

Hi All, I an a newbie to SQL queries and need to select rows from a contact DB where Address, City, State, Zip aren't null. I hope I am using the right lingo. Basically, if any of those fields are blank, I don't want it. I tried using city not is null, zip not is null, etc. but the query returns tons of <NULL>s. Can someone point me in the right direction? thanks!

 

[johnherman]

SELECT select fields, separated by commas FROM ContactDB
WHERE Address IS NOT NULL AND City IS NOT NULL AND State IS NOT NULL AND Zip IS NOT NULL

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[richardrekos]

No luck, I still get tons of <Null>s for the fields I specfied to be not null. Any ideas? Here's my code:

SELECT DISTINCT Company, Contact, Address1, Address2, City, State, Zip, Country
FROM Contact1
WHERE (COMPANY IS NOT NULL)AND(CONTACT IS NOT NULL) AND (TITLE IS NOT NULL) AND (ADDRESS1 IS NOT NULL) AND
(ADDRESS2 IS NOT NULL) AND (CITY IS NOT NULL) AND (ZIP IS NOT NULL) AND (STATE IS NOT NULL) AND
(COUNTRY = 'United States') OR
(COUNTRY = 'US') OR
(COUNTRY = 'usa') OR
(COUNTRY = 'U.S.') OR
(COUNTRY = 'u.s.a.') OR
(COUNTRY = 'U.S') OR
(COUNTRY = 'Canada')OR
(COUNTRY = 'u.s.a')

 

[gradley]

I believe your first OR statement after your NULL tests is causing the problem. You have a bunch of AND statements which will work fine on there own. You should wrap all the COUNTRY = tests in paranthesis. Better still, make the country test with IN.

Ex:

AND (Country IN ('united states', 'US', 'usa', 'u.s.', 'u.s.a.', 'canada')

 

[gradley]

I forgot to add the last parenthesis

AND (Country IN ('united states', 'US', 'usa', 'u.s.', 'u.s.a.', 'canada'))

 

[richardrekos]

Who's better than you? Works wonderfully now!! Thanks so much.

Rich

 

[gradley]

Anytime!