[SOLVED] SQL Server : SELECT ID having only a single condition

SQL Server : SELECT ID having only a single condition

I have a patients table with details such as conditions that the patient has. from the below table I want to select Patients, Claims which have ONLY a single condition - 'Hypertension'. Example Patient B is the expected output. Patient A will not be selected because he claimed for multiple conditions.

+----+---------+--------------+
| ID | ClaimID |  Condition   |
+----+---------+--------------+
| A  |   14234 | Hypertension |
| A  |   14234 | Diabetes     |
| A  |   63947 | Diabetes     |
| B  |   23853 | Hypertension |
+----+---------+--------------+

I tried using the NOT IN condition as below but doesn't seem to help

SELECT ID, ClaimID, Condition 
FROM myTable 
WHERE Condition IN ('Hypertension') 
  AND Condition NOT IN ('Diabetes')

Solution

One method uses not exists:

select t.*
from mytable t
where t.condition = 'Hypertension' and
      not exists (select 1
                  from mytable t2
                  where t2.id = t.id and t2.condition <> t.condition
                 );