RE: Need an urgent help in StoredProcedures

RE: Need an urgent help in StoredProcedures

 

  

How about, for clarity:

select * from table1
where feild1 in
(select field1 from table1
minus
select field2 from table2)


>From: christine.[Email Address Removed] "LazyDBA.com Discussion" <[Email Address Removed] RE: Need an urgent help in StoredProcedures
>Date: Thu, 29 Apr 2004 11:45:42 +0100
>
>
>This should work - your table is called table1 and has two columns - col1
>and col2
>
>select * from table1 t1 where
>not exists (select 1 from table1 t2
>where t2.col2 = t1.col1)
>
>Regards
>Christine
>
>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>Christine Carter
>DBA, PricewaterhouseCoopers
>Tel: 760 2657 (Internal), +44 (161) 245 2657 (External)
>christine.[Email Address Removed] "Vallipuram,
> Suganthan" To: "LazyDBA.com
>Discussion" <[Email Address Removed] <[Email Address Removed] cc:
> .com> Subject: RE: Need an
>urgent help in StoredProcedures
>
> 29/04/2004 10:44
>
>
>
>
>
>
>
>
>Hi
>Thanks, But my problem is nit that
>
>Say as an example
>
>Field1 field2
>101 203
>102 100
>103 101
>104 103
>105 106
>
>I want the numbers from field1 are 102,104,105 because 101 and 103 are
>appeared in the field 2 please consider that if we write as you mentioned
>it will give all the values (from 101 to 105).
>
>please suggest a solution for this problem
>
>Thanks in advanced
>
>With Best Regards,
>Suganthan,V
>Mobile : +94-77-7807639
>
> > -----Original Message-----
> > From: [Email Address Removed] > Sent: Thursday, April 29, 2004 3:40 PM
> > To: Vallipuram, Suganthan
> > Subject: RE: Need an urgent help in StoredProcedures
> >
> > So as an example you have this data:
> >
> >
> > Field1 Field2
> > 001 256
> > 002 458
> > 003 003
> > 004 357
> >
> >
> > you want a view/query that would return:
> >
> > Field1 Field2
> > 001 256
> > 002 458
> > 004 357
> >
> >
> >
> > As the below shows a number in field1 that is equal to field2
> >
> > Field1 Field2
> > 003 003
> >
> >
> > The query for this is simply (off the top of my head):
> >
> > SELECT dbo.Table1.*, field1 AS Expr1, field2 AS Expr2
> > FROM dbo.Table1
> > WHERE (field1 <> field2)
> >
> >
> > Cheers
> > David Postlethwaite
> > The Bank of New York
> > London SecLend UK IT
> > Tel: (0)207 964 6855
> > Email: [Email Address Removed] >
> >
> >
> >
> > "Vallipuram,
> >
> > Suganthan" To: "LazyDBA.com
> > Discussion" <[Email Address Removed] > <[Email Address Removed] cc:
> >
> > ix.com> Subject: RE: Need an
> > urgent help in StoredProcedures
> >
> >
> > 29/04/2004 10:09
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > 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
> > > > > > 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
> > > > > 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
> > 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] >
> >
> >
> >
> >
> >
> >
> > ________________________________________________________________________
> > The information in this e-mail, and any attachment therein, is
> > confidential and for use by the addressee only. If you are not the
> > intended recipient, please return the e-mail to the sender and delete it
> > from your computer. Although The Bank of New York attempts to sweep
>e-mail
> > and attachments for viruses, it does not guarantee that either are
> > virus-free and accepts no liability for any damage sustained as a result
> > of viruses.
>
>---------------------------------------------------------------------
>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] End of message text --------------------
>
>This e-mail is sent by the above named in their
>individual, non-business capacity and is not on
>behalf of PricewaterhouseCoopers.
>
>PricewaterhouseCoopers may monitor outgoing and incoming
>e-mails and other telecommunications on its e-mail and
>telecommunications systems. By replying to this e-mail you
>give your consent to such monitoring
>
>
>
>
>---------------------------------------------------------------------
>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] worrying about overloading your inbox - get MSN Hotmail Extra Storage!
http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1/go/onm00200362ave/direct/01/

MS Sql Server LazyDBA home page