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