values exists in one table but 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

General SQL Server Forums

New to SQL Server Programming

values exists in one table but not in another.

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2008-12-10 : 07:51:42

How can I find which rows in table1 that does not exists in table2.
Pretty basic but I am actually having trouble with it.

I have tried several joins but dont get the correct answer.

table1
id int
name char(10)
primary key(id)

table2
id int
table1_id int (foreign key to table1)
prinary key(id)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-12-10 : 08:14:26

select * from table1 as t1
left join table2 as t2 on (t1.id = t2.table1_id)
where t2.table2_id is null

or use

select * from table1 as t1
where not exist(select * from table2 as t2 where t2.table1_id=t1.id)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

maevr
Posting Yak Master

169 Posts

Posted - 2008-12-10 : 08:27:23

Thanks, I didn't think of nested selects.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources