Find Fields that are True

Question

Find Fields that are True

terrygefael

SSC Rookie

Points: 29
More actions
January 5, 2013 at 10:07 am

#287284

Hi, Folks! Need help.
I have a simple table with 29 fields(unique id, 14 True/False, 14 text)
I want to find all fields relating to one unique ID that are true.
Example:
SELECT *
FROM table
WHERE ID = 1
(But then I want the fields that are True)
Out of 14 fields, 4 might be true and the rest are false.
I want to create a list of True fields.
Any ideas?
Thanks in advance.
Terry

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

terrygefael (1/5/2013)
Hi, Folks! Need help.
I have a simple table with 29 fields(unique id, 14 True/False, 14 text)
I want to find all fields relating to one unique ID that are true.
Example:
SELECT *
FROM table
WHERE ID = 1
(But then I want the fields that are True)
Out of 14 fields, 4 might be true and the rest are false.
I want to create a list of True fields.
Any ideas?
Thanks in advance.
Terry

Lets say Column2, Column3, Column4 and Column5 all have values of "True" for ID IN (1,27,42). Your SELECT list might be unique id, Column2, Column3, Column4 and Column5, 14 text. What happens for ID IN (2,3,4,5) which might have values of "True" in Column3 and Column7? The SELECT list will be different: unique id, Column3, Column7, 14 text. The SELECT lists are not compatible.

There is a way of getting around this; concatenate all of the "True" values into a new column. You'd have to include the column name too.

If you're retrieving the result for a single ID then it's fairly straightforward, using "CROSS APPLY VALUES". Post the ddl for the table so someone can construct the query for you.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

terrygefael SSC Rookie Points: 29 More actions · Answer 2

The text fields are irrelevant at this stage as they are only going to be used if a field is designated as True. So, I thought about using a Case statement in my SQL, but am getting a "Missing Operator" error message. I should point out that the SQL is being used in a web page, if that makes a difference.

Here is what I came up with:

SELECT ID,

case when FB_chk = -1 Then ' FB_chk=True' else '' end

+ case when TW_chk = -1 Then ' TW_chk=True' else '' end

+ case when PI_chk = -1 Then ' PI_chk=True' else '' end

+ case when FR_chk = -1 Then ' FR_chk=True' else '' end

+ case when RS_chk = -1 Then ' RS_chk=True' else '' end

+ case when FS_chk = -1 Then ' FS_chk=True' else '' end

+ case when YT_chk = -1 Then ' YT_chk=True' else '' end

+ case when DS_chk = -1 Then ' DS_chk=True' else '' end

+ case when MU_chk = -1 Then ' MU_chk=True' else '' end

+ case when LN_chk = -1 Then ' LN_chk=True' else '' end

+ case when TB_chk = -1 Then ' TB_chk=True' else '' end

+ case when FF_chk = -1 Then ' FF_chk=True' else '' end

+ case when SU_chk = -1 Then ' SU_chk-True' else '' end

+ case when PC_chk = -1 Then ' PC_chk=True' else '' end

+ case when IG_chk = -1 Then ' IG_chk=True' else '' end

+ case when RD_chk = -1 Then ' RD_chk=True' else '' end

+ case when EM_chk = -1 Then ' EM_chk=True' else '' end as Result

FROM tbl_links

WHERE ID = 1

AND (FB_chk = -1 OR TW_chk = -1 OR PI_chk = -1 OR FR_chk = -1

OR RS_chk = -1 OR FS_chk = - 1 OR YT_chk = -1 OR DS_chk = -1

OR MU_chk = -1 OR LN_chk = - 1 OR TB_chk = -1 OR FF_chk = -1

OR SU_chk = -1 OR PC_chk = -1 OR IG_chk = -1 OR RD_chk = -1

OR EM_chk = -1)

But when I run it, I get a "Missing Operator" error.

So, again, I am stuck. Someone out there knows a fix to my dilemma.

Thanks in advance for thoughts, help.

Terry

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

It looks ok to me, Terry. Try this:

SELECT

ID,

Result =

case when FB_chk = -1 Then ' FB_chk=True' else '' end

+ case when TW_chk = -1 Then ' TW_chk=True' else '' end

+ case when PI_chk = -1 Then ' PI_chk=True' else '' end

+ case when FR_chk = -1 Then ' FR_chk=True' else '' end

+ case when RS_chk = -1 Then ' RS_chk=True' else '' end

+ case when FS_chk = -1 Then ' FS_chk=True' else '' end

+ case when YT_chk = -1 Then ' YT_chk=True' else '' end

+ case when DS_chk = -1 Then ' DS_chk=True' else '' end

+ case when MU_chk = -1 Then ' MU_chk=True' else '' end

+ case when LN_chk = -1 Then ' LN_chk=True' else '' end

+ case when TB_chk = -1 Then ' TB_chk=True' else '' end

+ case when FF_chk = -1 Then ' FF_chk=True' else '' end

+ case when SU_chk = -1 Then ' SU_chk-True' else '' end

+ case when PC_chk = -1 Then ' PC_chk=True' else '' end

+ case when IG_chk = -1 Then ' IG_chk=True' else '' end

+ case when RD_chk = -1 Then ' RD_chk=True' else '' end

+ case when EM_chk = -1 Then ' EM_chk=True' else '' end

FROM tbl_links

WHERE ID = 1

AND -1 IN (FB_chk, TW_chk, PI_chk, FR_chk, RS_chk, FS_chk, YT_chk, DS_chk,

MU_chk, LN_chk, TB_chk, FF_chk, SU_chk, PC_chk, IG_chk, RD_chk, EM_chk)

^{“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

terrygefael SSC Rookie Points: 29 More actions · Answer 4

Celko,

I definitely appreciate your taking the time to respond to my post.

The forum name is "SQL Server Newbies". I had assumed maybe that would be taken into account.

While I will do my best to follow forum etiquette,

I have no clue what you are talking about. What is DDL? And the various ISO spec's are greek to me.

Properly formed code: The code I offered up is directly from my web page. How much more formed does it need to be?

Maybe we need a forum here for SQL xxx, I don't know, what's less informed than a newbie?

-- Frustrated in a "SQL newbie forum"...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

terrygefael (1/7/2013)
Celko,
I definitely appreciate your taking the time to respond to my post.
The forum name is "SQL Server Newbies". I had assumed maybe that would be taken into account.
While I will do my best to follow forum etiquette,
I have no clue what you are talking about. What is DDL? And the various ISO spec's are greek to me.
Properly formed code: The code I offered up is directly from my web page. How much more formed does it need to be?
Maybe we need a forum here for SQL xxx, I don't know, what's less informed than a newbie?
-- Frustrated in a "SQL newbie forum"...

First, I know it's hard (especially for me) but you need to ignore Mr. Celko. It isn't the message but the messenger and how he presents the message that is the problem.

To help you, DDL means Data Definition Language. This is your CREATE TABLE, CREATE PROCEDURE, etc type statements.

You should take the time to read the first article I reference below in my signature block. It will walk you through what you need to post and how to do it.