CODEWITHSUNDEEP
mysqlsql
CM.
CM.

Reputation: 680

get specific records based on where condition

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.

Upvotes: 0

Views: 683

Answers (5)

Revan
Revan

Reputation: 1144

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271111

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;

Upvotes: 1

Ergec
Ergec

Reputation: 11834

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

Upvotes: 0

Ajay
Ajay

Reputation: 6590

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

Upvotes: 0

Matthias
Matthias

Reputation: 280

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

Upvotes: 0

Related Questions