query help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

query help

Author

Topic

learntsql

524 Posts

Posted - 2011-01-11 : 08:25:54

Hi All,
i have few tables and performing left join on those tables
like
Table1 -- Status column has to update depends on the entry for IsDeleted column in Table2.
because i am performaing left join and in select statemnt i wrote sample query like

declare @table1 table(ID int,status bit,Experience int)
declare @table2 table(ID int,ISDeleted bit)
declare @table2 table(ID int,DOJ datetime)

insert into @table1
select 1,null
union
select 2,null

for @table2 there are no entries for time being(there may be...)

select t1.*,t2.ISDeleted
from @table1 t1
left join @table2 t2
on t1.ID = t2.ID

--update statement like this
update t1
set t1.status = case when t2.ISDeleted = 1 then 1 else 0 end
from @table1 t1
left join @table2 t2
on t1.ID = t2.ID

select * from @table1

this is wrong because even if there are no entries in table2 also its showing either null or false based on else condition in case.
Please guide me how to change the query.

and for same statement i have to left join few more tables.

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-11 : 08:56:12

Do you understand the different JOIN types?

http://www.devguru.com/technologies/t-sql/7115.asp

===
http://www.ElementalSQL.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:12:42

no entries in table2 means Isdeleted is NULL then it will satisfy only the else condition of your case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2011-01-11 : 13:43:04

quote:
Originally posted by visakh16

no entries in table2 means Isdeleted is NULL then it will satisfy only the else condition of your case

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Exactly. Since you are using a LEFT JOIN you are going to get the NULL values and since NULL relates to tenery logic (three valued) and you are only checking if a condition is true you get a bad result. In SQL NULL represents an unknown value. This means that in your CASE since the condition is not true it defaults to 0. You should also check if the value is NULL.

===
http://www.ElementalSQL.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources