SQL Exclude rows on specific conditions

Question

I want to exclude in my MSSQL View some results which are "duplicated with null value"

stock_id	Current	BrandName	Made_in
1	1	X	Canada
1	1	NULL	NULL
2	1	y	USA
3	1	z	Nigeria
4	1	T	Cambodia

So I want to exclude the stock ID which are duplicated and NULL in the columns brand_name and made_in. Results :

stock_id	Current	BrandName	Made_in
1	1	X	Canada
2	1	y	USA
3	1	z	Nigeria
4	1	T	Cambodia

thats my try but it exclude now both stock_id

WHERE table.is_current = 1 and not (stock_id='1' and brand_name=null);

Answer 1

To exclude nulls you can use max function.

select stock_id,
       Current,
       max(BrandName) as BrandName ,
       max(Made_in) as Made_in
from test_tbl
group by stock_id,Current;

If you have nulls on current you can apply max(Current), too.

Demo

Answer 2

This should display your null also I apologize didn't saw that you are telling don't include id = 1 and brand_name = null

select * from tbl_stock where BrandName is not NULL group by stock_id