get specific records based on where condition

Question

I have a mysql table with country, state, city and locality. I want to get only those countries which are having either city or locality as null or empty. I tried this query:

select distinct country from xyz where state != "" and ((city="" and Locality="") or (city="" and Locality!="") or (city!="" and Locality="")) order by country

Basically need to fetch all the countries where either city or locality value is empty. This query gives me few countries which are having city and locality both in same row. What am I doing wrong? Its giving me countries which are having city and Locality both values.

I need country list which doesn't have city or Locality which means all the cities or locality of the country is empty or null. Do not want country if even one record of the country has either city or locality value.

Answer 1

Are you looking for a simple or:

select distinct country
from xyz
where state <> '' and
      (city = '' or Locality= '')
order by country;

If this doesn't return what you want, you might have a problem with NULL values:

select distinct country
from xyz
where state <> '' and
      (city = '' or Locality= '' or city is null or Locality is null)
order by country;

Or possibly the condition on state is not needed.

By the way, you should use single quotes rather than double quotes for string constants in SQL.

EDIT:

If you want a query where all the values are empty or NULL for a given country, then use aggregation and a having clause:

select country
from xyz
group by contry
having min(city = '' or Locality= '' or city is null or Locality is null) > 0
order by country;

Answer 2

select distinct country
from xyz
where state != ""
and (city="" or Locality="")

Answer 3

select distinct country from xyz where (state != "" and (city="" OR Locality=""))

Answer 4

Try to use IsNull property.

SELECT DISTINCT country
FROM xyz
WHERE state <> '' AND
  (city = '' OR Locality= '' OR city IS NULL OR Locality IS NULL)
ORDER BY country;

An int value can never contain the value ''. If you have any int value in city or Locality

Answer 5

need to use or instead of and

select distinct country from xyz where state <> "" and (city="" or city is null or Locality="" or Locality is null) order by country