Not in 2 criteria - 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

Not in 2 criteria

Author

Topic

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-15 : 03:16:29

I have 2 table - Tbl1 and Tbl2

Tbl1

ID1 ID2 Description
-------------------------

Tbl2

ID1 ID2 Rate
-------------------------

the select statement that i want is
select ID1, ID2 from tbl1 where ID1 and ID2 NOT IN Tbl2.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-15 : 03:23:53

[code]
select *
from Tbl1 t1
where not exists
(
select *
from Tbl2 t2
where t2.ID1 = t1.ID1
and t2.ID2 = t1.ID2
)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

calvinkwoo3000
Yak Posting Veteran

98 Posts

Posted - 2009-06-15 : 03:37:06

quote:

Originally posted by khtan


select *
from   Tbl1 t1
where  not exists
       (
           select *
           from   Tbl2 t2
           where  t2.ID1 = t1.ID1
           and    t2.ID2 = t1.ID2
       )

KH
[spoiler]Time is always against us[/spoiler]

Thanks KH Tan. This what i want.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-15 : 04:56:17

[code] try this one too
select *
from Tbl1 t1
LEFT JOIN Tbl2 t2 ON t2.ID1 = t1.ID1 and t2.ID2 = t1.ID2
WHERE t2.id1 is null
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-15 : 05:54:38

Also see http://weblogs.sqlteam.com/peterl/archive/2009/06/12/Timings-of-different-techniques-for-finding-missing-records.aspx

E 12°55'05.63"
N 56°04'39.26"