CODEWITHSUNDEEP
sql-server
Docu
Docu

Reputation: 147

Get rows with some null value in all his columns

I have a query that returns, for example, this results

ID   Name      Year  Age
0    NULL      2013  23
1    Luis      NULL  24
2    Jose      2010  NULL
3    Fernando  2003  43

I want to get all rows if for some columns (in this case Name,Year,Age) at least one row has a null value, else 0 rows. For example, in exposed example I get 4 rows because each column has at least one null value.

On the other hand:

ID   Name      Year  Age
0    NULL      2013  23
1    Luis      NULL  24
2    Jose      2010  34 
3    Fernando  2003  43

Age has no null values so I get 0 rows.

Thanks in advance!

Upvotes: 0

Views: 100

Answers (2)

Alex Peshik
Alex Peshik

Reputation: 1515

Version of @mrida is perfect, but I used CTE with only counts calculations to easier support.

/* test tables:
create table t1 (ID int,Name varchar(100),[Year] int, Age int)

insert t1
select 0,NULL,2013,23 union all
select 1,'Luis',NULL,24 union all
select 2,'Jose',2010,NULL union all
select 3,'Fernando',2003,43

create table t2 (ID int,Name varchar(100),[Year] int, Age int)

insert t2
select 0,NULL,2013,23 union all
select 1,'Luis',NULL,24 union all
select 2,'Jose',2010,34 union all
select 3,'Fernando',2003,43
*/

--for Jose with undefined age
with cte as (select count(*) as AllCount,count(year) as YearsCount,count(name) as NamesCount,count(age) as AgesCount from t1)
select t1.* from t1,cte
where not (cte.AllCount=cte.YearsCount or cte.AllCount=cte.NamesCount or cte.AllCount=cte.AgesCount)

--for 34-aged Jose :)
with cte as (select count(*) as AllCount,count(year) as YearsCount,count(name) as NamesCount,count(age) as AgesCount from t2)
select t2.* from t2,cte
where not (cte.AllCount=cte.YearsCount or cte.AllCount=cte.NamesCount or cte.AllCount=cte.AgesCount)

Upvotes: 1

mrida
mrida

Reputation: 1157

Use this:

  with cte as (
    select case when count(*)=count(year) or count(*) = count(name) or count(*)=count(age) then 0 else 1 end as val from data
    )
    select data.* from data,cte where 1 = cte.val

Upvotes: 4

Related Questions