How to get a record if any of it's column values is set to true ?

Question

How to get a record if any of it's column values is set to true ?

judejay26

SSC Enthusiast

Points: 107
More actions
June 19, 2018 at 6:34 am

#406726

I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
September 17, 2018 at 2:46 pm

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Henrico Bekker One Orange Chip Points: 27652 More actions · Answer 1

Your question lacks all the basics of asking good questions here.
I will attempt to answer you anyway, but please supply DDL and sample data for us to assist you better.
In SQL Server, it is known as BIT data type, 1 is converted to TRUE, 0 to FALSE.

So a basic query would look something like:

select columnname from table where bitcondition_column = 1

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle

Joe Torre SSChampion Points: 10248 More actions · Answer 2


DECLARE @t table(Id int IDENTITY PRIMARY KEY, bit1 bit, bit2 bit, bit3 bit, bit4 bit, bit5 bit, bit6 bit, bit7 bit, bit8 bit);
INSERT @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8)
VALUES (0, 1, 0, 0, 1, 0, 0, 0), (0, 0, 1, 0, 0, 1, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0), (1, 0, 0, 0, 1, 0, 0, 0)
     , (0, 1, 0, 0, 1, 0, 0, 0), (0, 1, 1, 0, 0, 0, 0, 0), (1, 0, 0, 0, 0, 0, 1, 0), (1, 0, 0, 0, 1, 0, 0, 0);
     
SELECT
   t.Id,t.bit1,t.bit2,t.bit3,t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
FROM 
   @t t
WHERE Cast(t.bit1 AS int) + Cast(t.bit2 AS int) + --Find rows with no bits set
      Cast(t.bit3 AS int) + Cast(t.bit4 AS int) +
      Cast(t.bit5 AS int) + Cast(t.bit6 AS int) +
      Cast(t.bit7 AS int) + Cast(t.bit8 AS int) = 0;
--Find rows with any bits set
SELECT t.Id, t.bit1, t.bit2, t.bit3, t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
FROM 
   @t t
WHERE Cast(t.bit1 AS int) 
    + Cast(t.bit2 AS int) 
    + Cast(t.bit3 AS int) 
    + Cast(t.bit4 AS int)
    + Cast(t.bit5 AS int)
    + Cast(t.bit6 AS int)
    + Cast(t.bit7 AS int)
    + Cast(t.bit8 AS int) > 0;

Thom A SSC Guru Points: 99133 More actions · Answer 3

Personally, i would go with a bunch of ORs or an IN. Using Joe's Sample Table:

SELECT *
FROM @t
WHERE bit1 = 1
  OR bit2 = 1
  OR bit3 = 1
  OR bit4 = 1
  OR bit5 = 1
  OR bit6 = 1
  OR bit7 = 1
  OR bit8 = 1;
SELECT *
FROM @t
WHERE 1 IN (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8);

Casting the values (to an int) might cause the query to become non-SARGable; so if you have a lot of rows (not records, SQL Server doesn't have records) and any indexes on those columns you might find these faster.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

andycadley SSCertifiable Points: 5296 More actions · Answer 4

I'd do it with bitwise arithmetic:

If it was a large enough table and a query that runs a lot, I'd make that a calculated column so that I could index it directly.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 5

Don't forget that, unless there's a NOT NULL constraint on these columns, a NULL could end up being the fly in the ointment.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 6

Here is one possible solution using a persisted calculated column (as already mentioned), with the addition of handling NULLs
😎
Can you post the DDL (create table) script for the table please, we are making the assumption that the datatype is a bit?
USE TEEST; GO SET NOCOUNT ON;

DECLARE @t table ( Id int IDENTITY PRIMARY KEY , bit1 bit , bit2 bit , bit3 bit , bit4 bit , bit5 bit , bit6 bit , bit7 bit , bit8 bit , BFLAG AS (ISNULL(SIGN(bit1),0) + ISNULL(SIGN(bit2),0) + ISNULL(SIGN(bit3),0) + ISNULL(SIGN(bit4),0) + ISNULL(SIGN(bit5),0) + ISNULL(SIGN(bit6),0) + ISNULL(SIGN(bit7),0) + ISNULL(SIGN(bit8),0)) PERSISTED ); INSERT @t (bit1, bit2, bit3, bit4, bit5, bit6, bit7, bit8) VALUES (0, 1, 0, 0, 1, 0, 0, 0), (0, 0, 1, 0, 0, 1, 0, 0), (0, 0, 0, 0, 0, 0, 0, 0), (1, 0, 0, 0, 1, 0, 0, 0) , (0, 1, 0, 0, 1, 0, 0, 0), (0, 1, 1, 0, 0, 0, 0, 0), (1, 0, 0, 0, 0, 0, 1, 0), (1, 0, 0, 0, 1, 0, 0, 0) , (1, 0, 0, 0, NULL, 0, 0, 0), (NULL, 0, 0, 0, NULL, 0, 0, 0); SELECT t.Id,t.bit1,t.bit2,t.bit3,t.bit4, t.bit5, t.bit6, t.bit7, t.bit8 ,t.BFLAG FROM @t t WHERE T.BFLAG > 0;

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 7

judejay26 - Tuesday, June 19, 2018 6:34 AM
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true

your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 8

jcelko212 32090 - Friday, August 3, 2018 8:38 AM
judejay26 - Tuesday, June 19, 2018 6:34 AM
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.

While I agree that many people overuse the concept of flags, flags are an important part of data. Even MS uses them in their system tables and views. For example, IS_PrimaryKey and IS_Unique. Without those flags, there's a whole bunch of things you couldn't do in SQL Server

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76997 More actions · Answer 9

jcelko212 32090 - Friday, August 3, 2018 8:38 AM
judejay26 - Tuesday, June 19, 2018 6:34 AM
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.

Rows are representations of records, so they are, in fact, like records to the point that the two terms are used interchangeably.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 10

jcelko212 32090 - Friday, August 3, 2018 8:38 AM
judejay26 - Tuesday, June 19, 2018 6:34 AM
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.

Joe, can I ask you a favor, can you please post a solution that substantiate your post?
😎

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 11

Eirikur Eiriksson - Friday, August 3, 2018 9:49 AM
jcelko212 32090 - Friday, August 3, 2018 8:38 AM
judejay26 - Tuesday, June 19, 2018 6:34 AM
I have a table which has 20 or so boolean True/false. How do I find a record in the table which has any bool set to true
your approach to SQL is wrong. Rows are nothing like records. We don't write SQL with flags; that was assembly language programming. SQL is what I call a predicate language; we discover the state of being in the data by using predicates, and not by setting flags. If you will post the DDL and a better description of what you're trying to do, perhaps we can help you.
Joe, can I ask you a favor, can you please post a solution that substantiate your post?
😎

He is correct about one thing... the OP needs to post the DDL for the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)