SQL Query Help - 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 2000 Forums

Transact-SQL (2000)

SQL Query Help

Author

Topic

biotx
Starting Member

1 Post

Posted - 2006-12-12 : 15:25:27

This should be a pretty simple query, just something I've never had experience with though.

Say I have two tables each with one column called Num. Table 1 has numbers 1-25, Table 2 has numbers 1,5,10,15,20, and 25. What would be the query to select all rows from Table 1 that aren't in Table 2? That, or what would be the query to remove all rows from Table 1 that are in Table 2?

I basically I need the numbers 1-25 returned but without 1,5,10,15,20,and 25.

Any help would be greatly appreciated.

cognos79
Posting Yak Master

241 Posts

Posted - 2006-12-12 : 15:30:00

This is the query to "select all rows from Table 1 that aren't in Table 2"

select * from table1 t1
where t1.num not in (select t2.num from table2
where t1.num = t2.num)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-12 : 23:34:36

or using Not Exists:

Select num from Tbl1 t1
where not exists (select * from Tbl2 t2 where t1.num = t2.num)

For deleting comman records:

Delete t1
From tbl1 t1 join tbl2 t2
on t1.num = t2.num

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources