find records in one table which are not in another

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)

find records in one table which are not in another

Author

Topic

shmecher
Starting Member

10 Posts

Posted - 2005-01-17 : 09:53:49

Hi. I need to find the records that are in a table t1 and not in table t2.
The problem is that there is a primary key on 2 fields defined on both tables, so I can't use:
select * from t1 where t1.field1 not in (select t2.field1 from t2)

Right now, I use: select * form t1 where t1.field1 + t1.field2 not in (select t2.field1 + t2.field2 from t2)
(field1 and field2 are unique ident.)
but I think it's stupid...

Thanks
TIBM

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2005-01-17 : 09:55:15

Use an exists predicate.

SELECT <your columns>
FROM t1
WHERE NOT EXISTS(SELECT * FROM t2 WHERE t2.field1 = t1.field1 AND t2.field2 = t1.field2)

Dustin Michaels

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-01-17 : 09:56:27

will this do?

select t1.*
from t1 left join t2 on t2.field1 = t1.field1 and t2.field2 = t1.field2
where t2.field1 is null and t2.field2 is null

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

shmecher
Starting Member

10 Posts

Posted - 2005-01-17 : 10:21:18

Great ideas. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources