Running selected not exists on two tables on two

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)

Running selected not exists on two tables on two

Author

Topic

akalehzan
Starting Member

21 Posts

Posted - 2008-02-29 : 11:35:51

Hi all,

I have the following SQL script that works fine, but I like to view all the fields for the records that are not exists in “capdb.dbo.abc “ for “capdb2.dbo.abc” table instead of some fields :


select distinct cp2abc.subj_num , cp2abc.abc_age,  cp2abc.ABC_LETHARGY, cp2abc.ABC_STEREOTYPY
, cp2abc.ABC_STEREOTYPY, cp2abc.ABC_HYPERACTIVITY, cp2abc.ABC_INAPPROPRIATE_SPEECH


from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc

where not exists 

(select  cp1abc.subj_num, cp1abc.abc_date from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num 

	and cp2abc.abc_DATE = cp1abc.abc_date)

I tried cp2abc.*, but the returns entire the records in the tables.


select cp2abc.*

from capdb2.dbo.abc as cp2abc, capdb.dbo.abc as cp1abc

where not exists 

(select  cp1abc.subj_num from capdb.dbo.abc as cp1abc where cp2abc.subj_num = cp1abc.subj_num 

	and cp2abc.abc_DATE = cp1abc.abc_date)

Is there a way to accomplish this

Thanks for any help.

Regards,

Abrahim

(moved from Script Library by Jeff)

nathans
Aged Yak Warrior

938 Posts

Posted - 2008-02-29 : 22:05:20

Are you familiar with LEFT JOIN syntax?

For example, if I have two tables:

TableA (TestName varchar(10))
TableB (TestName varchar(10))

And I want to return all "TestName" values present in TableA but NOT IN TableB, I would use:


select a.TestName
from   TableA a
left
join   TableB b on
       a.TestName = b.TestName
where  b.TestName is null

I hope this helps get you started. Please post back if you have difficulty.

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources