Sql - query multiple lines

Question

Sql - query multiple lines

I have a table with names and a bit column indicating if the name is delted (1) or not (0). I am trying to write a query that returns all deleted names, unless the name is also deleted (the name can appear more than once in the table). Hope this makes sense!

Here are some sample data:

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

So the result will be Bob

and Sam

:
Both records "Bob" are "1".
Single entry Sam - 1.

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

Thanks for any help!

+3

sql mysql

chipper3344 Jul 20. 17 at 20:29

source to share

2 answers

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)

+1

n8wrl Jul 20. 17 at 20:31

source to share

Gordon linoff · Accepted Answer · 2017-07-20T20:32:08+0000

One method uses aggregation:

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

Sql - query multiple lines

More articles: