Exists Clauses - 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

Transact-SQL (2000)

Exists Clauses

Author

Topic

kappa02
Yak Posting Veteran

65 Posts

Posted - 2005-02-18 : 10:09:27

Correct me if I'm wrong but the results should only return id's from TblA that are not in tblB correct?

select id from TblA
where not exists(select * from TblB
where tblA.id = TblB.companyid)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-18 : 10:26:17

yup.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 10:28:46

Correct.

- spirit's in da house...
You can also use a left join
select a.id
from tbla a left join tblb b on a.id = b.companyid
where b.companyid is null

rockmoose

kappa02
Yak Posting Veteran

65 Posts

Posted - 2005-02-18 : 10:33:56

One would think that it's so But I find the value of the id of 37778 in both tables? And yes I try using the left Join as well as re-writting it with "Not In". Could it be there is something wrong with the indexes on both tables?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-18 : 10:48:41

well you counld try to drop indexes, or rebuild them and try again.
if the id is a varchar or like then maybe you have some trailing spaces somewhere in there. do ltrin(rtrim(id)).

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 10:51:09

Well id 37778 is in both tables, is it not ?
What result are you expecting ?

rockmoose

kappa02
Yak Posting Veteran

65 Posts

Posted - 2005-02-18 : 10:55:09

I expect to see 37778 in only Tbla because I ask for id's that exists in tbla and not tblb?

kappa02
Yak Posting Veteran

65 Posts

Posted - 2005-02-18 : 10:55:38

I expect to see 37778 in only Tbla because I ask for id's that exists in tbla and not tblb.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-18 : 10:58:58

could you post some DDL and DML? crate table and insert into statements for sample data that's not working for you.
this way we can see what your problem is.

Go with the flow & have fun! Else fight the flow

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-02-18 : 11:09:58

You mean that 37778 is NOT in tblb, but the sql will not return it.
sorry to ask, but you did do:
select * from tblb where companyid = 37778 ????
select * from tbla where id = 37778

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources