Retrieving data - 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 2005 Forums

Transact-SQL (2005)

Retrieving data

Author

Topic

azvelos
Starting Member

1 Post

Posted - 2009-06-18 : 08:02:06

Hi
I have two tables
how do I retrieve records which exists in one table but not in the other (accordding to a common field that have a different name but same type)?

nr
SQLTeam MVY

12543 Posts

Posted - 2009-06-18 : 08:10:22

select t1.*
from tbl1 t1
left join tbl2.t2
on t1.fld1 = t2.fld2
where t2.fld2 is null

select *
from tbl1
where fld1 not in (select distinct fld2 from tbl2)

select *
from tbl1 t1
where not exists (select * from tbl2 t2 where t2.fld2 = t1.fld1)
lots of other ways

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 08:28:42

In 2005 , you can use

select * from tbl1
except
select * from tbl2

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-06-18 : 08:30:58

and Nigel's first query should be

select t1.*
from tbl1 t1
left join tbl2 t2
on t1.fld1 = t2.fld2
where t2.fld2 is null

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources