why NOT IN cluase not working?

[jaideep]

Dear sql guru's

 

I am problem with "not in" clause.

 

I am writing the following query.

 

FormB.uid is primary key and it has got all the records related to uid in other forms.

 

select uid from formB where uid NOT IN
((select formD.uid from formD ,formF where
formD.uid=formF.uid)
Union
(select formE.uid from formE ,formF where
formE.uid=formF.uid)
Union
(select formE.uid from formE ,formG where
formE.uid=formG.uid))

 

This is not working as I am aslo getting those uid which are present both formD and formF but are not getting eliminated.

 

please help
jaideep

[Curt Hagenlocher]

"jaideep" <sonu...@id.eth.net> wrote in message news:<000a01c12b94$c1721d40$0301a8c0@avishkar>...

> FormB.uid is primary key and it has got all the records related to uid
> in other forms.
>
> select uid from formB where uid NOT IN
> ((select formD.uid from formD ,formF where
> formD.uid=formF.uid)
> Union
> (select formE.uid from formE ,formF where
> formE.uid=formF.uid)
> Union
> (select formE.uid from formE ,formG where
> formE.uid=formG.uid))
>
> This is not working as I am aslo getting those uid which are present
> both formD and formF but are not getting eliminated.

Given the limited amount of information you have provided, this works for
me under SQL Server 7 SP2. I set up the data as follows:

create table formB (uid int not null)
create table formD (uid int not null)
create table formE (uid int not null)
create table formF (uid int not null)
create table formG (uid int not null)
go
insert formB values (1)
insert formD values (1)
insert formF values (1)
go

... and my result set from your query is empty.

--
Curt Hagenlocher
cu...@hagenlocher.org