get rows on a tbl which does not exist 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

get rows on a tbl which does not exist in another

Author

Topic

chessmonster
Starting Member

6 Posts

Posted - 2011-06-02 : 12:43:59

hi, sorry for the unclear title.

I just would like to have some ideas on how I can retrieve rows that are not existing on another table. (it sounds unclear) well it like I have two tables

table1
col1, col2
1, a
2, b
3, c
4, d
5, e

and

table2
col1, col2
2, b
4, d

i have two tables here, what I would like to do if possible is to get the rows in table1 that are not existent in table2.

result ids should be 1,3,5 coming from table.

tried to do the <> operator but it seems like a failure. maybe some ideas folks? :D any would be appreciated

toughness and stubborness

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-02 : 12:49:04

One possibility:


select
   col1,
   col2
from
   table1 t1
where
   not exists( select * from table2 t2 where t1.col1 = t2.col1 and t1.col2 = t2.col2)

Alternatively,


select
   t1.col1,
   t2.col2
from
   table1 t1
   left join table2 t2 on
      t1.col1 = t2.col1 and t1.col2 = t2.col2
where
   t2.col1 is not null

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-02 : 12:50:38

select col1
from table1
except
select col1
from table2

select * from table1
where col1 not in (select col1) from table2

select t1.col1
from table1 t1
left join table2 t2 on t1.col1 = t2.col1
where t2.col1 is null

Jim

Everyday I learn something that somebody else already knew

chessmonster
Starting Member

6 Posts

Posted - 2011-06-02 : 13:10:50

thanks for the both of the people who answered. both suggestion were helpful and importantly gave me some ideas.

thanks ! solved it, used the not exists method

toughness and stubborness

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources