Why does it return only one row

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 2000 Forums

Transact-SQL (2000)

Why does it return only one row

Author

Topic

azamsharp
Posting Yak Master

201 Posts

Posted - 2008-11-10 : 16:41:52

Why does the select statement with the IN clause only return the 1 row. How can I return both the rows using In and NULL in the table.

create table #k1
(
id uniqueidentifier,
areaid uniqueidentifier
)

select * from #k1
where areaid in ('0C8A28F4-5A35-4160-ACEA-BE3FAC62F5CC',NULL)

insert into #k1 (id, areaid)
values(newid(), newid())

insert into #k1 (id, areaid)
values(newid(), null)

Mohammad Azam
www.azamsharp.net

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-10 : 16:46:03

select * from #k1
where areaid = '0C8A28F4-5A35-4160-ACEA-BE3FAC62F5CC' or areaid is NULL

select * from #k1
where coalesce(areaid, '0C8A28F4-5A35-4160-ACEA-BE3FAC62F5CC') = '0C8A28F4-5A35-4160-ACEA-BE3FAC62F5CC'

E 12°55'05.63"
N 56°04'39.26"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-11-10 : 16:47:16

WHER areaid IS NULL OR areaid IN (...)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

azamsharp
Posting Yak Master

201 Posts

Posted - 2008-11-10 : 16:50:21

Thanks for the reply! Your query works!! But is there any way to achieve this only using the T-SQL IN operator.

Mohammad Azam
www.azamsharp.net

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-11-10 : 18:00:12

Another option is to COALESCE the AreaID:
select * from #k1
where COALESCE(CAST(areaid AS VARCHAR(36)), '') in ('0C8A28F4-5A35-4160-ACEA-BE3FAC62F5CC','')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources