How to create SQL Operators Combination like ALL IN, NOT ALL IN

Question

I have below 4 properties in a Filter class. I am going to parse below 4 properties to a StoredProcedure and get the filtered result.

/// <summary>
/// Gets and sets comma seperated condition ids.
/// Patients must have all these conditions in order to satisfy the filter.
/// </summary>
public string MustHaveAllConditions { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must not have all of these conditions in order to satisfy the filter.
/// </summary>
public string MustNotHaveAllConditions { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must have at least one of these conditions in order to satisfy the filter.
/// </summary>
public string MustHaveAtLeastOneCondition { get; set; }

/// <summary>
/// Gets and sets comma separated condition ids.
/// Patients must not have at least one of these conditions in order to satisfy the filter.
/// </summary>
public string MustNotHaveAtLeastOneCondition { get; set; }

My Stored procedure will have four parameters like below. Eg:

@*MustHaveAll*Conditions = "1,2"

@*MustNotHaveAll*Conditions = "3,4"

@*MustHaveAtLeastOne*Condition = "5,6,7,8"

@*MustNotHaveAtLeastOne*Condition = "9, 10"

I am using a UDF that returns a table with Ids column.

My question:

Basically I can use SQL "IN" operator to find the patients who has at least one Condition (ie : @MustHaveAtLeastOneCondition ) and "NOT IN" operator combination to filter @MustNotHaveAnyConditions.

Are there any SQL Operators(or esay ways) to filter MustHaveAllConditions, MustNotHaveAllConditions parameters ?

score 1 · Accepted Answer · answered May 16 '11 at 05:50

-- Patients
declare @Patient table (PatientID int)

-- Conditions per patient
declare @PatientCondition table (PatientID int, ConditionID int)

-- Conditions table generated from param string
declare @Condition table (ConditionID int)

-- Test data
insert into @Patient
select 1 union all
select 2 union all
select 3

insert into @PatientCondition
select 1, 1 union all
select 1, 2 union all
select 1, 3 union all
select 2, 1 union all
select 3, 3

insert into @Condition
select 1 union all
select 2


-- MustHaveAll
select *
from @Patient as P
where P.PatientID in 
  (
    select PC.PatientID
    from @PatientCondition as PC
      inner join @Condition as C
        on PC.ConditionID = C.ConditionID
    group by PC.PatientID
    having count(PC.ConditionID) = (select count(ConditionID) from @Condition)
  )

--MustNotHaveAll
select *
from @Patient as P
where P.PatientID not in 
  (
    select PC.PatientID
    from @PatientCondition as PC
      inner join @Condition as C
        on PC.ConditionID = C.ConditionID
    group by PC.PatientID
    having count(PC.ConditionID) = (select count(ConditionID) from @Condition)
  )

-- MustHaveAtLeastOne
select *
from @Patient as P
where P.PatientID in
  (
    select PC.PatientID
    from @PatientCondition as PC
      left outer join @Condition as C
        on PC.ConditionID = C.ConditionID
    where C.ConditionID is not null
  )

--MustNotHaveAtLeastOne
select *
from @Patient as P
where P.PatientID not in
  (
    select PC.PatientID
    from @PatientCondition as PC
      left outer join @Condition as C
        on PC.ConditionID = C.ConditionID
    where C.ConditionID is not null
  )

This excellent. Can you tell me the most efficient way if I want to merge these four conditions in a single sql query in order to get the final list of patients? — CharithJ, May 16 '11 at 06:08
@CharithJ – You could just add the where clauses from the different queries to one query and separate with `and`. `where PatientID in (...) and PatientID in (...) and ...` etc. — Mikael Eriksson, May 16 '11 at 06:17

score 0 · Answer 2 · answered May 16 '11 at 03:22

There really isn't an easy way to do what you're describing. What I would do is probably take your string and split it into a table variable and then use a combination of right, left and inner joins to remove results from another table variable that was my output.

How to create SQL Operators Combination like ALL IN, NOT ALL IN

2 Answers2