sql - query multiple rows

Question

I have a table with names and a bit column indicating whether the name is delted (1) or not (0). I'm trying to write a query that returns all names that are deleted, unless the name is also not deleted (a name may show up more than once in the table). Hope that makes sense!

Here is some sample data:

+------+-------+
|Name  |Deleted|
+------+-------+
|Bob   |   1   |
+------+-------+
|Joe   |   1   |
+------+-------+
|Joe   |   0   |
+------+-------+
|Bob   |   1   |
+------+-------+
|Sam   |   1   |
+------+-------+

So the result would be Bob and Sam:
Both 'Bob' entries are '1'.
Single Sam entry is '1'.

Joe would not be in the results because he is both '1' and '0'.

Thanks for any help!

Answer 1

One method uses aggregation:

select name
from t
group by name
having min(deleted) = 1;

Answer 2

SELECT T.Name
  FROM Table T
 WHERE T.Deleted = 1
   AND NOT EXISTS (SELECT *
                     FROM Table T2
                    WHERE T2.Name = T.Name
                      AND T2.Deleted = 0)