Need to get all ids which are not in table2

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 2008 Forums

Transact-SQL (2008)

Need to get all ids which are not in table2

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-10-10 : 12:57:09

table1: subacct

table2 : subacct

is this the proper way to get the not found subacct's

select distinct subacct from table1
where subacct not in (select subaccts from table2)

Thanks a lot for the helpful info.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 13:15:40

I would use NOT EXISTS or LEFT JOIN/IS NULL

select subacct
from table1
where not exists (select * from table2 where table1.subacct = table2.subacct)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 13:16:19

select table1.subacct
from table1
left join table2
on table1.subacct = table2.subacct
where table2.subacct is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2014-10-10 : 14:08:33

Thank you TARA.

quote:
Originally posted by tkizer

select table1.subacct
from table1
left join table2
on table1.subacct = table2.subacct
where table2.subacct is null

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 14:11:49

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources