NOT IN clause subquery - 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 2000 Forums

SQL Server Development (2000)

NOT IN clause subquery

Author

Topic

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-30 : 14:18:53

Guys,

I have the following queries, I confused/puzzled at to why last query returns 0 rows

select * from nplats
--- 12811

select book, page1, book+page1 from nplats
where ltrim(rtrim(book))+ltrim(rtrim(page1)) in (select ltrim(rtrim(book))+ltrim(rtrim(page)) from document)
order by book
--- 2693

select book, page1, book+page1 from nplats
where ltrim(rtrim(book))+ltrim(rtrim(page1)) not in (select ltrim(rtrim(book))+ltrim(rtrim(page)) from document)
order by book
--- 0

I have 12811 rows in the table nplats, When I use 1st query I get 2693. Technically 2nd query should give me 10118
(12811 - 2693) rows, on the contrary it gives me 0 rows. I dont know what mistake I am doing here. All colums (book, page, page1) across both the tables have same datatype varchar(10).

Any suggestions/inputs would be helpful

Thanks

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-01-30 : 14:31:31

There's a NULL in document.book or document.page somewhere.

scelamko
Constraint Violating Yak Guru

309 Posts

Posted - 2006-01-30 : 15:13:43

Yepp you are right, thanks a ton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources