T-SQL exactly 1 condition is true out of a set of conditions

Question

What is the simplest way to express in T-SQL that only 1 and exactly 1 of a number of boolean conditions is true (needs to be usable in a CHECK constraint)?

XOR works for 2 conditons, eg A XOR B will insure that exactly 1 is set but it does not work for 3 conditions:

One solution would be to get some kind of collection out of them, filter on the conditions being true, perform and aggregate/sum and check that the result is equal to 1.

It may not be clear to people that the truth table you're showing is demonstrating *how XOR doesn't work for you*, rather than *what your expected result is*, which I've interpreted should be as shown except with `F` in the top line rather than `T`. — Damien_The_Unbeliever, Jan 10 '17 at 11:01
@Damien_The_Unbeliever I thought it was clear that the table shows how it works, not how it should work cause I mentioned this before: `but it does not work for 3 conditions:` — Răzvan Flavius Panda, Jan 10 '17 at 11:17

Damien_The_Unbeliever · Answer 1 · 2017-01-10T11:11:42.277

5

I would structure your CHECK along the lines of:

CHECK (
    CASE WHEN <condition 1> THEN 1 ELSE 0 END +
    CASE WHEN <condition 2> THEN 1 ELSE 0 END +
    CASE WHEN <condition 3> THEN 1 ELSE 0 END
    = 1
)

It's slightly verbose but it is hopefully readable to see what your intention was. It also extends to other similar requirements more easily than XOR (e.g. "exactly 2 out of 5 conditions must be matched" can follow the same structure)

For SQL Server 2012 or later, you can be slight more concise with IIF:

CHECK (
    IIF(<condition 1>,1,0) +
    IIF(<condition 2>,1,0) +
    IIF(<condition 3>,1,0)
    = 1
)

edited Jan 10 '17 at 11:11

answered Jan 10 '17 at 10:57

Damien_The_Unbeliever

234,701
27
340
448

That would be a solution but still there is a lot of code duplication in it. See the update in my question. – Răzvan Flavius Panda Jan 10 '17 at 11:00
@RăzvanPanda - I don't think the verbosity can be avoided. As you may be aware, SQL Server doesn't have a user-visible boolean data type - so you can't do something along the lines you propose since there's no way to represent intermediate results as *data*. – Damien_The_Unbeliever Jan 10 '17 at 11:03
@RăzvanPanda - and also, obviously, this structure extends to cover other requirements more easily than an XOR based one (e.g. if you're requirement was "exactly 2 out of 5 conditions must be satisfied", you can trivially adjust the above to match that) – Damien_The_Unbeliever Jan 10 '17 at 11:06
Any idea if it is possible to map over boolean conditions (since they can not be stored in a table literal)? See the answer I added for what I mean – Răzvan Flavius Panda Jan 10 '17 at 12:37

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

Shamelessly taken from this SO question, a general formula for an exclusive OR between three variables can be written as:

(a ^ b ^ c) && !(a && b && c)

We can express this in SQL Server as:

(A XOR B XOR C) AND NOT (A AND B AND C)

Note that this only works for three variables, and does not generalize to higher numbers. If you have more than three variables, you'll have to do more work.

edited May 23 '17 at 12:24

Community

1
1

answered Jan 10 '17 at 11:01

Tim Biegeleisen

502,043
27
286
360

Just wanted to post this :) – Giorgi Nakeuri Jan 10 '17 at 11:03

score 1 · Answer 3 · answered Jan 10 '17 at 11:19

Assuming that all your conditions are represented as BIT columns, you can have a constraint with the format:

alter table [table_name] add constraint [constraint_name]
check ( ( a ^ b ^ c ) = 1 AND NOT ( a & b & c ) = 1 )

Doing this, you can also then use the same conditions in a case statement, like:

select a, b, c,
    case when (( a ^ b ^ c ) = 1 AND NOT ( a & b & c ) = 1) then 1
    else 0 end
    as true_or_false
from [table_name]

Putting this together, we can demo it with a script like:

create table #bits  (a bit, b bit, c bit)
create table #bits2  (a bit, b bit, c bit)

alter table #bits2 add constraint ck_xor
    check ( ( a ^ b ^ c ) = 1 AND NOT ( a & b & c ) = 1 )

insert into #bits
values
( 0, 0, 0 ), ( 0, 0, 1 ), ( 0, 1, 0 ), ( 0, 1, 1 ), ( 1, 0, 0 ), ( 1, 0, 1 ), ( 1, 1, 0 ), ( 1, 1, 1 )

select a, b, c,
    case when ( a ^ b ^ c ) = 1 AND NOT ( a & b & c ) = 1 then 1
    else 0 end
    as true_or_false
from #bits

insert into #bits2
select * from #bits
where ( a ^ b ^ c ) = 1 AND NOT ( a & b & c ) = 1 

-- the below line will fail because of the check constraint    
insert into #bits2 (a,b,c) values (1,1,0)

select * from #bits2

drop table #bits
drop table #bits2

score 0 · Answer 4 · answered Jan 10 '17 at 11:23

Declare three variable @a, @b , @c and it's bit type. Where 1 is true and 0 is false. Example with all possible of bit is here.

  declare @a bit, @b bit, @c bit;
set  @a=1; set @b=1;  set @c=1; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=1; set @b=1;  set @c=0; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=1; set @b=0;  set @c=0; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=1; set @b=0;  set @c=0; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;

set  @a=0; set @b=1;  set @c=0; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=0; set @b=1;  set @c=0; select @a as a,@b as b,@c as c, case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=0; set @b=0;  set @c=0; select @a as a,@b as b,@c as c, case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;
set  @a=0; set @b=0;  set @c=0; select @a as a,@b as b,@c as c,  case when @a=@b then (case when @c=1 then 1 else 0 end) else (case when @c=0 then 1 else 0 end) end as xor;

score 0 · Answer 5 · answered Jan 10 '17 at 12:34

To reduce even more from the code duplication and make it a bit more readable something like this could be used:

(SELECT SUM(ExpressionValue)
FROM
    (VALUES
        (CASE WHEN 42 = 42 THEN 1 ELSE 0 END),
        (CASE WHEN 42 = 42 THEN 1 ELSE 0 END),
        (CASE WHEN 42 = 1 THEN 1 ELSE 0 END),
        (CASE WHEN 42 = 42 THEN 1 ELSE 0 END)
    ) AS conditions(ExpressionValue))
=
1

Example usage:

DECLARE @say as VARCHAR(MAX) =
    CASE
        WHEN (
            (SELECT SUM(ExpressionValue)
            FROM
                (VALUES
                    (CASE WHEN 42 = 42 THEN 1 ELSE 0 END),
                    (CASE WHEN 42 = 42 THEN 1 ELSE 0 END),
                    (CASE WHEN 42 = 1 THEN 1 ELSE 0 END),
                    (CASE WHEN 42 = 42 THEN 1 ELSE 0 END)
                ) AS conditions(ExpressionValue))
            =
            1
        ) THEN 'Only one set'
        ELSE 'Non only one set'
    END

PRINT @say

It could still be improved if it is somehow possible to elegantly just apply the case operation similar to a map.

score 0 · Answer 6 · answered Jan 10 '17 at 22:44

0

declare @tt table (i int, b1 bit, b2 bit, b3 bit);
insert into @tt values (1,0,0,0), (2,1,1,1), (3,1,0,0)
select i, b1, b2, b3 
from @tt 
where cast(b1 as tinyint) + cast(b2 as tinyint) + cast(b3 as tinyint) = 1

answered Jan 10 '17 at 22:44

paparazzo

44,497
23
105
176

T-SQL exactly 1 condition is true out of a set of conditions

6 Answers6