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!

Gordon Linoff · Accepted Answer · 2017-07-20 20:32:08Z

3

One method uses aggregation:

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

answered Jul 20, 2017 at 20:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

n8wrl Over a year ago

Clever! I like it

n8wrl · Accepted Answer · 2017-07-20 20:31:47Z

1

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)

answered Jul 20, 2017 at 20:31

n8wrl

19.9k4 gold badges70 silver badges106 bronze badges

Collectives™ on Stack Overflow

sql - query multiple rows

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related