I would make two changes to that solution:-
select field1 from someTable
where not exists(select 'X' from someTable T2
where field2 = someTable.field1)
you do not need to fetch the data within the subquery as you are only
checking for exists, this will be wasteful on a large table and also
requires extra processing to build the list of fields if you say SELECT * -
you only want a boolean indicator.
Add a non-clustered index on field2 so that the exists lookup can be done by
an index seek.
Regards
Keith
-----Original Message-----
From: Vallipuram, Suganthan [ <mailto:[Email Address Removed] 29 April 2004 10:09
To: LazyDBA.com Discussion
Subject: RE: Need an urgent help in StoredProcedures
Hi,
The solution doesn't support me.
Again I will say the problem.
I have only one table and this table contains 2 fields and both fields are
integer. The first one is a unique one (AutoNumber). The second field may
contain any integer. Now I want the numbers from the field1(AutoNumber) but
the condition is these numbers should not be in field2 in the same table.
Please suggest a solution which should run fast, because there a lot of
datas in this table.
Thanks in advanced
With Best Regards,
Suganthan,V
Mobile : +94-77-7807639
> -----Original Message-----
> From: O15_11_2002 [SMTP:[Email Address Removed] Sent: Thursday, April 29, 2004 2:54 PM
> To: Vallipuram, Suganthan
> Subject: Re: Need an urgent help in StoredProcedures
>
> your welcome : )
>
> ----- Original Message -----
> From: "Vallipuram, Suganthan" <[Email Address Removed] To: "O15_11_2002" <[Email Address Removed] Sent: Thursday, April 29, 2004 11:48 AM
> Subject: RE: Need an urgent help in StoredProcedures
>
>
> > HI,
> > Thank you so much. I will try it now
> >
> > With Best Regards,
> > Suganthan,V
> >
> >
> > > -----Original Message-----
> > > From: O15_11_2002 [SMTP:[Email Address Removed] > > Sent: Thursday, April 29, 2004 2:15 PM
> > > To: LazyDBA.com Discussion
> > > Subject: Re: Need an urgent help in StoredProcedures
> > >
> > > 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] > > To: "LazyDBA.com Discussion" <[Email Address Removed] > > Sent: 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>
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] > > >
> > >
> > > ---------------------------------------------------------------------
> > > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > > Get today's cartoon: <http://www.LazyDBA.com> 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] >
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Get today's cartoon: <http://www.LazyDBA.com> 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] NOTICE
This communication and the information it contains is intended for the person or organisation to
whom it is addressed. Its contents are confidential and may be protected in law. Unauthorised use,
copying or disclosure of any of it may be unlawful. If you are not the intended recipient, please
contact us immediately.
The contents of any attachments in this e-mail may contain software viruses, which could damage your
own computer system. While Marlborough Stirling has taken every reasonable precaution to minimise
this risk, we cannot accept liability for any damage, which you sustain as a result of software
viruses. You should carry out your own virus checking procedure before opening any attachment.
Marlborough Stirling plc, Registered in England and Wales
Registered No. 3008820,
Allen Jones House, Jessop Avenue, Cheltenham, Gloucestershire, GL50 3SH
Tel: 01242 547000 Fax: 01242 547100
http://www.marlborough-stirling.com
MS Sql Server LazyDBA home page