Getting IDs which have all NULL records

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 270
Declare @table table (order_number int, PONumber varchar(50))
insert into @table
SELECT 1, 'NULL_Value' union ALL
SELECT 1, 'NULL_Value' union ALL
SELECT 2, 'AAA' union ALL
SELECT 2, 'NULL_Value' union ALL
SELECT 3, 'AAA' union ALL
SELECT 3, 'AAA'


select * from @table a
where NOT EXISTS
(
SELECT * from @table b
where a.order_number = b.order_number
and PONumber not LIKE '%NULL_VALUE%'
)

output:
order_number PONumber
1 NULL_Value
1 NULL_Value

--Alternate approach

SELECT order_number
FROM @table
group by order_number
having count(*) = count(CASE WHEN PONumber = 'NULL_Value' then order_number END)

OUTPUT:
1

Comments or Responses

Login to post response