We help IT Professionals succeed at work.
Get Started

SQL Where clause to ensure 2 columns are not NULL at same time

137 Views
Last Modified: 2015-06-05
On the surface, this should be a simple query but I'm not getting the results I expect.
We have 3 columns I'm interested in from a larger table - let's call them Style, City1, and City2
An example data set may look like this:

Style    City1               City2
1          NULL         NULL
2          Boston          NULL
3          NULL          Chicago
4          NULL          NULL
5          New York          Dallas
2          Boston        NULL
2          Boston        Null

I need to write a query that:

Only returns rows where Style is not Null
*SKIPS the Row if BOTH City1 and City2 are Null

That second condition is messing me up.  I wrote the following but get no results:

      SELECT      Style
                  , UPPER(City1) as [City1]
                  , UPPER(City2) as [City2]
                                          
      FROM      ExampleTable

      WHERE      CityType in (1,2,3)
                  AND ((City1 is not null) and (City2 is not null))
                                    
      GROUP BY Style, City1, City2
      ORDER BY Style, City1, City2

This queries a subset of a larger table so there can be many rows where Style, City1 and City2 are the same.  Hence the Group By to reduce it down to distinct values.

CityType is another column in table, but I'm not showing it in the results.  

Here are the results I was expecting to get:

Style    City1             City2
2          Boston          NULL
3          NULL          Chicago
5          New York          Dallas

Instead, I get zero results.  Any thoughts?  Thanks!
Comment
Watch Question
SQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015
Commented:
This problem has been solved!
Unlock 1 Answer and 4 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE