Select ... not in - using multiple fields

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)

Select ... not in - using multiple fields

Author

Topic

emzzz
Starting Member

15 Posts

Posted - 2008-02-25 : 07:04:34

Hello,

I have a cross reference table which has been updated in one database and I want to put the new entries into my second database.

I have tried the following:
select * from tableA where fieldA, fieldB not in (select FieldA, FieldB from tableB)

but I get the following error:
An expression of non-boolean type specified in a context where a condition is expected, near ','.

From my search results, this will work in Access but what can I use for SQL Server.

Many thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-25 : 07:07:31

use not exists

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-25 : 07:13:48

or LEFT JOIN.

SELECT A.* 
FROM TABLEA A LEFT JOIN TABLEB B
ON A.fieldA = B.fieldA AND A.fieldB = B.fieldB
WHERE B.fieldA IS NULL AND B.fieldB IS NULL

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

emzzz
Starting Member

15 Posts

Posted - 2008-02-25 : 07:15:57

I've replaced not in with not exists in but it results with the same error.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-02-25 : 07:16:33

how did you replace it? did you even look at the exists syntax?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

emzzz
Starting Member

15 Posts

Posted - 2008-02-25 : 07:27:40

Many thanks for your help. I have used the except statement which gives me rows from the second query that are not in the first query.

eaglesql5
Starting Member

1 Post

Posted - 2010-04-06 : 14:59:13

Please post the solution, thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources