SQL 2005 - Syntax questions - 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

General SQL Server Forums

New to SQL Server Programming

SQL 2005 - Syntax questions

Author

Topic

o9z
Starting Member

23 Posts

Posted - 2008-01-29 : 10:07:25

What is the proper syntax to return records that appear in 1 table but NOT the other table? I have 2 tables that should contain the same records(based on shipping report number), so my join will use this field. How can I only return the data where the shipping report number appears in only 1 of the tables though>?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-01-29 : 10:10:08

[code]-- to return records which are in t1 but not in t2
t1 LEFT JOIN t2 on t1.key = t2.key
where t2.key IS NULL[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 10:15:22

Use either LEFT JOIN or NOT EXISTS.

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

select t2.*
from table2 as t2
where not exists (select * from table1 as t1 where t1.col1 = t2.col1)

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-29 : 10:22:07

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources