SQL Server : SELECT ID having only a single condition

Question

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') 

Answer 1

Use joins instead of subquery. Joins are always better in performance. You can use below query.

SELECT T1.id, T1.claimid, T1.Condition
FROM mytable T1
INNER JOIN 
(
    select id, count(Condition)  counter
    from mytable 
    group by id HAVING COUNT(DISTINCT CONDITION)=1
) T2 ON T1.ID=T2.ID
WHERE  T2.counter=1

Answer 2

SELECT id, sum(ct)
FROM (SELECT customer_id, CASE WHEN category = 'X' THEN 0 else 1 
end ct
FROM MASTER_TABLE
) AS t1
GROUP BY id
HAVING sum(ct) = 0

id which will have sum(ct) more than 1, will have multiple conditions

Answer 3

I decided to revise my answer into an appropriate one.

A simple solution to your question is to count the rows instead of the ID values (since it's not an integer).

Here is a simple introduction:

SELECT 
    ID 
FROM 
    #PatientTable 
GROUP BY 
    ID 
HAVING
    ID = ID AND COUNT(*) = 1 

This will Return the ID B

+----+
| ID |
+----+
| B  |
+----+

Surely, this is not enough, as you may work with a large data and need more filtering.

So, we will go and use it as a sub-query.

Using it as a sub-query it's simple :

SELECT
    ID, 
    ClaimID, 
    Condition
FROM 
    #PatientTable
WHERE 
    ID = (SELECT ID AS NumberOfClaims FROM #PatientTable GROUP BY ID HAVING ID = ID AND COUNT(*) = 1)

This will return

+----+---------+--------------+
| ID | ClaimID |  Condition   |
+----+---------+--------------+
| B  |   23853 | Hypertension |
+----+---------+--------------+

So far so good, but there is another issue we may face. Let's say you have a multiple Claims from a multiple patients, using this query as is will only show one patient. To show all patients we need to use IN rather than = under the WHERE clause

WHERE 
    ID IN (SELECT ID AS NumberOfClaims FROM #PatientTable GROUP BY ID HAVING ID = ID AND COUNT(*) = 1)

This will list all patients that falls under this condition.

If you need more conditions to filter, you just add them to the WHERE clause and you'll be good to go.

Answer 4

And yet a couple of other ways to do this:

    declare @TableA  table(Id char,
                           ClaimId int,
                           Condition varchar(250));
    insert into @TableA (id, claimid, condition)
                values ('A', 14234, 'Hypertension'),
                       ('A', 14234, 'Diabetes'),
                       ('A', 63947, 'Diabetes'),
                       ('B', 23853, 'Hypertension')       


    select id, claimid, condition
    from @TableA a
    where not exists(select id
                     from @TableA b
                     where a.id = b.id
                     group by b.id
                     having count(b.id) > 1)

    OR

    ;with cte as
   (
     select id, claimid, condition
     from @TableA
   )
   ,
   cte2 as
   (
     Select id, count(Id) as counts
     from cte
     group by id
     having count(id) < 2       
    )

   Select cte.id, claimid, condition
   From cte
   inner join
   cte2
   on cte.id = cte2.id

Answer 5

Here is one method using Having clause

SELECT t.*
FROM   mytable t
WHERE  EXISTS (SELECT 1
               FROM   mytable t2
               WHERE  t2.id = t.id
               HAVING Count(CASE WHEN condition = 'Hypertension' THEN 1 END) > 0
                      AND Count(CASE WHEN condition != 'Hypertension' THEN 1 END) = 0) 

Answer 6

Or you can do it like this:

select 
    id,
    claim_id,
    condition
from 
    patient
where 
    id in
    (
        select
            id
        from
            patient
        group by
            id having count (distinct condition) = 1
    );

Result:

id claim_id    condition
-- ----------- ----------------
B        23853 Hypertension

(1 rows affected)

Setup:

create table patient
(
    id         varchar(1),
    claim_id   int,
    condition  varchar(16)
);

insert into patient (id, claim_id, condition) values ('A', 14234, 'Hypertension');
insert into patient (id, claim_id, condition) values ('A', 14234, 'Diabetes');
insert into patient (id, claim_id, condition) values ('A', 63947, 'Diabetes');
insert into patient (id, claim_id, condition) values ('B', 23853, 'Hypertension');

Answer 7

You can do this with a CTE.

I set up this CTE with two parameters, one being the Condition you seek, and the other being the max number of combined conditions to find (in your case 1).

DECLARE @myTable TABLE (Id VARCHAR(1), ClaimID INT, Condition VARCHAR(100))
INSERT INTO @myTable (Id, ClaimID, Condition)
SELECT 'A',14234,'Hypertension' UNION ALL
SELECT 'A',14234,'Diabetes' UNION ALL
SELECT 'A',63947,'Diabetes' UNION ALL
SELECT 'B',23853,'Hypertension'

DECLARE @Condition VARCHAR(100)
DECLARE @MaxConditions TINYINT

SET @Condition='Hypertension'
SET @MaxConditions=1

; WITH CTE AS
(
    SELECT *, COUNT(2) OVER(PARTITION BY ClaimID) AS CN
    FROM @myTable T1
    WHERE EXISTS (SELECT 1 FROM @myTable T2 WHERE T1.ClaimID=T2.ClaimID AND T2.Condition=@Condition)
)

SELECT *
FROM CTE
WHERE CN<=@MaxConditions

If you don't care about the fluff, and just want all ClaimID's with just ONE condition regardless of which condition it is use this.

DECLARE @myTable TABLE (Id VARCHAR(1), ClaimID INT, Condition VARCHAR(100))
INSERT INTO @myTable (Id, ClaimID, Condition)
SELECT 'A',14234,'Hypertension' UNION ALL
SELECT 'A',14234,'Diabetes' UNION ALL
SELECT 'A',63947,'Diabetes' UNION ALL
SELECT 'B',23853,'Hypertension'

DECLARE @MaxConditions TINYINT

SET @MaxConditions=1

; WITH CTE AS
(
    SELECT *, COUNT(2) OVER(PARTITION BY ClaimID) AS CN
    FROM @myTable T1
)

SELECT *
FROM CTE
WHERE CN<=@MaxConditions

Answer 8

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
                 );