NOT IN in SQL Query Result not Shown

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

Transact-SQL (2005)

NOT IN in SQL Query Result not Shown

Author

Topic

raguyazhin
Posting Yak Master

105 Posts

Posted - 2013-12-28 : 02:41:45

Hi,

Result Not Shown in NOT IN Used Query.

The First Query Return 1 Row for the criteria

select * from RE_SURGERY_SCHEDULE where RSS_SURGERY_SCHEDULE_NUM='SG10131212/0003'

The Second Query Return 0 Row for the criteria

select * from WA_ADMISSION_TXN where WAT_SURGERY_SCHEDULE_NUM='SG10131212/0003'
Here I combine two queries but result not shown what is mistake in this query

select * from RE_SURGERY_SCHEDULE
where RSS_SURGERY_SCHEDULE_NUM not in (
select WAT_SURGERY_SCHEDULE_NUM from WA_ADMISSION_TXN)

--
Ragu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-28 : 03:18:26

you should be using NOT EXISTS I guess


select * from RE_SURGERY_SCHEDULE r
where NOT EXISTS(
select 1 from WA_ADMISSION_TXN WHERE r.RSS_SURGERY_SCHEDULE_NUM = WAT_SURGERY_SCHEDULE_NUM 
)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Kristen
Test

22859 Posts

Posted - 2013-12-28 : 09:31:26

[code]
select * from RE_SURGERY_SCHEDULE
where RSS_SURGERY_SCHEDULE_NUM not in (
select WAT_SURGERY_SCHEDULE_NUM from WA_ADMISSION_TXN) [/code]

will fail if the inner SELECT returns a NULL.

Does this return any rows?
[code]
select TOP 100 *
from WA_ADMISSION_TXN
WHERE WAT_SURGERY_SCHEDULE_NUM IS NULL
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources