Select person in a table having a specific value but not having another value for same id

I tried for hours and read many posts but I still can’t figure out how to handle this request:

I have a table like this:

+------+------+------+
|PERSON|TRTYPE| ID   |
+------+------+------+
|JERRY | I    |   2  |
+------+------+------+
|JERRY | U    |   2  |
+------+------+------+
|TOM   | U    |   2  |
+------+------+------+
|SPIKE | I    |   3  |
+------+------+------+
|SPIKE | U    |   3  |
+------+------+------+

I would like to select the PERSON, ID that has a TRTYPE of U for an ID but he does not have a TRYPE of I for that same ID.

The output should be something like:

+------+------+
|PERSON| ID   |
+------+------+
|TOM   | U    |
+------+------+

As TOM is the only person who has performed a transtype of U on an ID on which he did not perform a TRTYPE of I

Answer

Use aggregation and having:

select person, id
from mytable
where trtype in ('I', 'U')
group by person, id
having min(trtype) = max(trtype) and min(trtype) = 'U'

Advertisement

Answer