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

  • 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 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


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

  • 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

  • I'd do it with bitwise arithmetic:


    Select t.Id, t.bit1, t.bit2, t.bit3, t.bit4, t.bit5, t.bit6, t.bit7, t.bit8
    From
       @t t
    Where 
    (t.bit1 | t.bit2 | t.bit3 | t.bit4 | t.bit5 | t.bit6 | t.bit7 | t.bit8)<> 0

    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.

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

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

  • 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. 

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

  • 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

  • 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?
    😎

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

  • 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