Select only rows with common status from table in SQL Server

Question

I have table as follows:

ID CustomerID  AccountNumber StatusID
-------------------------------------
1     300          300100       1
2     300          300200       3
3     300          300300       3
4     400          400100       1
5     400          400200       1
6     500          500100       1

StatusID:

• 1 = Approved
• 3 = Pending

Now I need to select all customers whose accounts are approved (none are pending), but not customers whose accounts are still pending.

Please let me know if you need more details from my end.

Answer 1

Group by the customer and take only those having no status <> 1

select customerID
from your_table
group by customerID
having sum(case when status <> 1 then 1 else 0 end) = 0