CODEWITHSUNDEEP
sqlsql-server
Wilmer Ofelio Alama Seminario
Wilmer Ofelio Alama Seminario

Reputation: 148

Get only one row if is not equals

i have my table:

ID | NAME | TYPE             
1  | A    | 1    
2  | A    | NULL  
3  | B    | NULL  
4  | C    | 2    
5  | D    | NULL 
6  | D    | NULL

and i want to for example if i choose Type 1 i get the row but i want all the null values from the other.

ID | NAME | TYPE             
1  | A    | 1    
3  | B    | NULL  
5  | D    | NULL 
6  | D    | NULL

sometime like this. i try with union but i get repeat A | NULL

thx for all

Upvotes: 2

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You seem to want:

select t.*
from t
where type = 1
union all
select t.*
from t
where type is null and
      not exists (select 1 from t t2 where t2.name = t.name and t2.type = 1);

You don't actually need the union all:

select t.*
from t
where type = 1 or
      (type is null and
       not exists (select 1 from t t2 where t2.name = t.name and t2.type = 1)
      );

Upvotes: 4

Related Questions