yes, i think "not exists" better than "not in", may fastly
Suganthan, you should use "not exists"

regards
ali

----- Original Message -----
From: "Bart Hayes" <[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] Thursday, April 29, 2004 11:04 AM
Subject: RE: Need an urgent help in StoredProcedures


> A self join should do it:
>
> select field1 from someTable
> where not exists(select * from someTable T2
> where field2 = someTable.field1)
>
> -----Original Message-----
> From: Vallipuram, Suganthan [mailto:[Email Address Removed] Sent: Thursday, 29 April 2004 3:42 PM
> To: LazyDBA.com Discussion
> Subject: Need an urgent help in StoredProcedures
>
>
> > Dear Friends,
> >
> > In MSSQL server-7, I want to write a stored procedure in which I would
> > like to search a field value which should not be in another field. For
> > example
> >
> > Table1 and ite field are field1 and field 2 both are integer, but the
> > field1 is unique (AutoNumber).
> > I would like to get the values of field1, but these values should not be
> > in the field2.
> >
> > Please help me as soon as possible
> >
> >
> > Thanks in advanced
> >
> > With Best Regards,
> > Suganthan,V
> >
> >
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Get today's cartoon: http://www.LazyDBA.com
> To unsubscribe, e-mail: mssqldba-[Email Address Removed] ,
> then respond to the confirmation email you get
> For additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page