QUERY not in?

or..

select *
from a
where a.field1 not in
(
select b.field1
from b
where b.field2 = a.field2
)

(MsSQL 7.0)
-Kudel

"Joe \"Nuke Me Xemu\" Foster" <joe@bftsi0.UUCP> wrote:
>"Pat" <pwang@uclink.berkeley.edu> wrote in message <news:3cc60082$1@10.1.10.29>...
>
>> You can write a query like
>> select * from a where a.field1 not in (select field2 from b).
>>
>> But I tried to write a query with 2 fields ,
>> select * from a where (a.field1, a.field2) not in (select field1, field2
>> from b).
>>
>> This didnot work, does anybody know how to do this?
>
>You should be able to do this with NOT EXISTS:
>
>select * from a
>where not exists (select * from b
> where b.field1 = a.field1 and b.field2 = a.field2)
>
>An outer join should also work:
>
>select a.*
>from a left join b
> on a.field1 = b.field1 and a.field2 = b.field2
>where b.field1 is null and b.field2 is null
>
>--
>Joe Foster <mailto:jlfoster%40znet.com> Got Thetans? <http://www.xenu.net/>
>WARNING: I cannot be held responsible for the above They're coming
to
>because my cats have apparently learned to type. take me away,
ha ha!
>
>