T-SQL Help

Question

T-SQL Help

pvar

Newbie

Points: 4
More actions
December 9, 2008 at 10:06 am

#128343

Hi,
I have a table with 4 boolean fields. I need to find the records that have more than one boolean field set to true. That means records with A & b set to true and a & c set to true and a,b & c set to true etc.
Can any one help me with the SELECT statement for this? Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

pvar (12/9/2008)
Hi,
I have a table with 4 boolean fields. I need to find the records that have more than one boolean field set to true. That means records with A & b set to true and a & c set to true and a,b & c set to true etc.
Can any one help me with the SELECT statement for this? Thanks

What datatype are they? If not BIT, then can you provide values for true and false please.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

noeld SSC Guru Points: 96590 More actions · Answer 2

pvar (12/9/2008)
Hi,
I have a table with 4 boolean fields. I need to find the records that have more than one boolean field set to true. That means records with A & b set to true and a & c set to true and a,b & c set to true etc.
Can any one help me with the SELECT statement for this? Thanks

How about:

SELECT *

FROM

WHERE (a+b+c+d) > 1

* Noel

pvar Newbie Points: 4 More actions · Answer 3

I am sorry, I should have provided that info. The field type is tinyint so the values are 0 & 1. Thanks.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

pvar (12/9/2008)
I am sorry, I should have provided that info. The field type is tinyint so the values are 0 & 1. Thanks.

No problem. This will do:

SELECT * FROM (

SELECT CAST(0 AS BIT) AS a, CAST(0 AS BIT) AS b, CAST(0 AS BIT) AS c, CAST(0 AS BIT) AS d UNION ALL

SELECT 1, 0, 0, 0 UNION ALL

SELECT 1, 1, 0, 0 UNION ALL

SELECT 1, 1, 1, 0 UNION ALL

SELECT 1, 1, 1, 1 UNION ALL

SELECT 1, 0, 0, NULL

) d

WHERE ISNULL(a, 0) + ISNULL(b, 0) + ISNULL(c, 0) + ISNULL(d, 0) > 1

The ISNULLs are necessary!

cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pvar Newbie Points: 4 More actions · Answer 5

Thanks guys for your quick response. I tried Noeld's solution and it worked, I liked the simplicity of the select statement. But I am not sure about chris's solution, where do I put the table name & the field names?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

pvar (12/9/2008)
Thanks guys for your quick response. I tried Noeld's solution and it worked, I liked the simplicity of the select statement. But I am not sure about chris's solution, where do I put the table name & the field names?

Sorry pvar, the derived table simply provided test data to show that the statement worked. Use it like this:

SELECT * FROM YourTable WHERE ISNULL(a, 0) + ISNULL(b, 0) + ISNULL(c, 0) + ISNULL(d, 0) > 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pvar Newbie Points: 4 More actions · Answer 7

pvar

Newbie

Points: 4

December 9, 2008 at 11:08 am

#909821

Thanks chris, this works!

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 8

ISNULL (or COALESCE) is only required if the columns allow nulls. If the columns do not allow nulls, then you don't need it. For a column that contains true/false (0/1) - I would not allow nulls and would have a default defined.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs