Boolean column addition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Boolean column addition

Author

Topic

mastro78
Starting Member

9 Posts

Posted - 2008-11-05 : 10:57:57

Two tables (Table1, Table2), I'm looking to display Table1.Field1 and Table1.Field2 as well as a field that if Table1.Field3=Table2.Field3 Then NewField shows True Else False. A bit beyond my current scope. Please advise. Thank you.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-05 : 11:18:37

Probably a case statement..

I don't know the relationship between table1 and table 2 so here's some generic code.


SELECT
    t1.field1
    , t1.field2
    , CASE
        WHEN t1.field3 = t2.field3 THEN CAST(1 AS BIT)
        ELSE CAST (0 AS BIT)
    END AS [isSame]
FROM
    table1 t1
    <requried form of> JOIN table2 ON <your relationship>

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-05 : 11:21:10

Oh yeah -- this doesn't cope with nulls. You could do


CASE
    WHEN t1.[field3] IS NULL AND t2.[field3] IS NULL THEN 1
    WHEN t1.[field3] = t2.[field3] THEN 1
    ELSE 0
END

Assuming you want to treat NULLS as the same (which you may or may not wish to depending on what they represent in your data)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:25:10

or simply


CASE
WHEN COALESCE(t1.[field3],0) = COALESCE(t2.[field3],0) THEN 1
ELSE 0
END

mastro78
Starting Member

9 Posts

Posted - 2008-11-05 : 11:28:47

The join is Table1.Field3 = Table2.Field3 Then NewField = 1 Else shows no value (True/False more or less). How do I display the boolean field so my output looks like this:

Table1.Field1 Table1.Field2 NewField
sdjkfgsadfjkg asdkjfghsdf 1
asdfasdf asdjkf
asdfjhfhhh adjkfhsadkj 1

Etc.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:36:05

[code]SELECT
t1.field1
, t1.field2
, CASE
WHEN COALESCE(t1.[field3],0) = COALESCE(t2.[field3],0) THEN 1
ELSE 0
END AS NewField
FROM
table1 t1
LEFT JOIN table2 t2
ON t1.Field3 = t2.Field3[/code]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-05 : 11:51:04

Hi viskah.

I don't think your case statement is good.

It will compare a NULL to a 0 and return 1 when it probably shouldn't

Maybe Should be the following for logical correctness:


CASE
    WHEN t1.[field3] = t2.[field3] THEN 1
    WHEN t1.[field3] <> t2.[field3] THEN 0
    ELSE NULL
END

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 11:56:17

quote:
Originally posted by Transact Charlie

Hi viskah.

I don't think your case statement is good.

It will compare a NULL to a 0 and return 1 when it probably shouldn't

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

depends on what actual field contain. what if it wont contain 0 values otherwise.If 0 is concern then put some value which might not come otherwise

SELECT
    t1.field1
    , t1.field2
    , CASE
WHEN COALESCE(t1.[field3],-1) = COALESCE(t2.[field3],-1) THEN 1
ELSE 0
END  AS NewField
FROM
    table1 t1
LEFT JOIN table2 t2
ON t1.Field3 = t2.Field3

mastro78
Starting Member

9 Posts

Posted - 2008-11-05 : 12:03:38

With what you gave me the last part was confusing Table1 t1 and Table2 T2. Also, it should display all records not sure why the need for the join at the very end of the statement?

Thank you for all your help. It has been quite the learning experience.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-05 : 12:05:47

Actually, thinking about if. since the join in on field3 and is a LEFT JOIN the code can be this.


SELECT
	t1.[field1]
	, t1.[field2]
	, CASE
		WHEN t2.[field3] IS NULL THEN 'FALSE'
		ELSE 'TRUE'
	END AS [Match]
FROM
	table1 t1
	LEFT JOIN table2 t2 ON t1.[Field3] = t2.[Field3]

Because the join will take care of the comparison for us!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mastro78
Starting Member

9 Posts

Posted - 2008-11-05 : 13:09:42

FROM Table1 t1 - what does that mean exactly? I thought T1 stood for Table1?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-11-05 : 14:19:20

quote:
Originally posted by mastro78

FROM Table1 t1 - what does that mean exactly? I thought T1 stood for Table1?

It does. Look up alias in BOL. table1 = t1 if coded as above. This allows using the alias as opposed to the entire table name in further references, i.e. t1.[Field3] is the same as table1.[Field3] but fewer keystrokes.....

Terry

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 04:16:47

And, more importantly it means you can join to the same table more than once if you had to. As long as you gave each join to that table a unique alias.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources