Find out the IDs which doesn't have PENDING status.
Here the 123 ID is having PENDING status. So it shouldn't be in the final output
--Sample Data population
DECLARE @temp TABLE (status varchar(10), id int)
INSERT INTO @temp
SELECT 'Processed', 123
UNION ALL
SELECT 'complete', 123
UNION ALL
SELECT 'pending', 123
UNION ALL
SELECT 'Processed', 456
UNION ALL
SELECT 'complete', 456
-- Query to get the records whihc doesn't have status as 'pending'
SELECT status, ID
FROM @temp
WHERE status != 'Pending'
/*
Processed 123
complete 123
Processed 456
complete 456
*/
The above query gives incorrect results as 123 ID is haing
pending status.
To resolve this issue, the below two approaches will work fine.
-- First Approach
SELECT status, ID
FROM @temp
WHERE ID NOT IN (SELECT ID from @temp where status = 'pending')
-- second approach
select *
from @temp a
where NOT EXISTS
(
SELECT *
FROM @temp x
WHERE x.id = a.id
AND x.status = 'pending'
)
/* OUTPUT
status id
Processed 456
complete 456
*/