CODEWITHSUNDEEP
sqlbooleanlogicwhere-clause
HVMP
HVMP

Reputation: 72

Using Boolean to determine 5-way Where clause

I'm looking at 5 different columns (db made badly unfortunately). If of the five columns two have one "1" value and one "2" value I want this record to be excluded from the results. However, if it only has one of the two values I want it to be included.

I have this so far, but I'm certain it will not include the record if it has even one of the two values.

    NOT ((Ew.DocRecvd1 = 10 OR Ew.DocRecvd1 = 11) OR 
(Ew.DocRecvd2 = 10 OR Ew.DocRecvd2 = 11) OR 
(Ew.DocRecvd3 = 10 OR Ew.DocRecvd3 = 11) OR 
(Ew.DocRecvd4 = 10 OR Ew.DocRecvd4 = 11) OR 
(Ew.DocRecvd5 = 10 OR Ew.DocRecvd5 = 11))

Thanks.

Upvotes: 0

Views: 52

Answers (3)

HVMP
HVMP

Reputation: 72

OK, I think I've found the result I wanted.

I used the following in the WHERE clause of my query:

 NOT
     (2 =
     (CASE WHEN Ew.DocRecvd1 = 10 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd2 = 10 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd3 = 10 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd4 = 10 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd5 = 10 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd1 = 11 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd2 = 11 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd3 = 11 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd4 = 11 THEN 1 ELSE 0 END
      +
     CASE WHEN Ew.DocRecvd5 = 11 THEN 1 ELSE 0 END))

It is only possible in my DB to get these two documents in one of five places within one record, so the count could not go over 2 with the two documents i'm looking for.

Kudos to Nicholas Carey and Gordon Linoff for keying me into what I could do and look for!

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74277

You state the filter conditions simply in the where clause. Given a table

create table foobar
(
  id int not null primary key ,
  c1 int not null ,
  c2 int not null ,
  c3 int not null ,
  c4 int not null ,
  c5 int not null ,
)
go

You can say

select *
from foobar
where not (     2 = case c1 when 1 then 1 else 0 end
                  + case c2 when 1 then 1 else 0 end
                  + case c3 when 1 then 1 else 0 end
                  + case c4 when 1 then 1 else 0 end
                  + case c5 when 1 then 1 else 0 end
            and 1 = case c1 when 2 then 1 else 0 end
                  + case c2 when 2 then 1 else 0 end
                  + case c3 when 2 then 1 else 0 end
                  + case c4 when 2 then 1 else 0 end
                  + case c5 when 2 then 1 else 0 end
          )

The other approach which might run faster is to use as mask table, containing the conditions you want to exclude. Something like this one:

create table mask
(
  c1 tinyint null ,
  c2 tinyint null ,
  c3 tinyint null ,
  c4 tinyint null ,
  c5 tinyint null ,
  unique clustered ( c1,c2,c3,c4,c5) ,
)

In your case, there are only 30 conditions to be excluded:

c1   c2   c3   c4   c5
---- ---- ---- ---- ----
NULL NULL 1    1    2
NULL NULL 1    2    1
NULL NULL 2    1    1
NULL 1    NULL 1    2
NULL 1    NULL 2    1
NULL 1    1    NULL 2
NULL 1    1    2    NULL
NULL 1    2    NULL 1
NULL 1    2    1    NULL
NULL 2    NULL 1    1
NULL 2    1    NULL 1
NULL 2    1    1    NULL
1    NULL NULL 1    2
1    NULL NULL 2    1
1    NULL 1    NULL 2
1    NULL 1    2    NULL
1    NULL 2    NULL 1
1    NULL 2    1    NULL
1    1    NULL NULL 2
1    1    NULL 2    NULL
1    1    2    NULL NULL
1    2    NULL NULL 1
1    2    NULL 1    NULL
1    2    1    NULL NULL
2    NULL NULL 1    1
2    NULL 1    NULL 1
2    NULL 1    1    NULL
2    1    NULL NULL 1
2    1    NULL 1    NULL
2    1    1    NULL NULL

(30 row(s) affected)

The actual query is trivial then (and if you have a covering index on the columns to be tested, the test is done with index seeks and so should perform extremely well:

select *
from dbo.foobar t
where not exists ( select *
                   from mask m
                   where t.c1 = m.c1
                     and t.c2 = m.c2
                     and t.c3 = m.c3
                     and t.c4 = m.c4
                     and t.c5 = m.c6
                 )

The advantage of this approach is that the ruleset is table-driven, meaning future changes to the rules are just data modifications to your mask table.

You could also use a positive set of rules, but in your case, the set is bigger (>200 positive cases as opposed to the 30 negative cases).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would suggest that you count the number of values in each group that you want. And, I would do it in a subquery, just because that makes the code more readable and maintainable.

Here is an example:

from (select t.*,
             ((case when Ew.DocRecvd1 in (10, 11) then 1 else 0) +
              (case when Ew.DocRecvd2 in (10, 11) then 1 else 0) +
              (case when Ew.DocRecvd3 in (10, 11) then 1 else 0) +
              (case when Ew.DocRecvd4 in (10, 11) then 1 else 0) +
              (case when Ew.DocRecvd5 in (10, 11) then 1 else 0) +
             ) as Num1s,
             <something similar> as Num2s
      from table t
     ) t
where Num1s = 2 and Num2s = 1;

Upvotes: 1

Related Questions