Skip to Content.
Sympa Menu

freetds - RE: [freetds] SQL Query troubles

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Michael Peppler <mpeppler AT peppler.org>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] SQL Query troubles
  • Date: Thu, 08 Jan 2004 11:18:42 -0800

On Thu, 2004-01-08 at 11:08, Lowden, James K wrote:
> > From: Steven Orton [mailto:sorton9999 AT usa.net]
> > Sent: January 8, 2004 1:57 PM
> >
> > I seem to be having problems with the following query:
> >
> > SELECT * FROM table1 WHERE Target_ID=(SELECT DISTINCT
> > Target_ID FROM table2
> > WHERE PlatformTypeID=62 AND db_ctx_id=3)
>
> It's not a good idea to rely on a subquery returning only one row to a point
> evaluation. How do you expect the server to cope if it returns two
> (distinct) Target_IDs?
>
> I would do:
>
> SELECT * FROM table1
> WHERE Target_ID in (
> SELECT DISTINCT Target_ID
> FROM table2
> WHERE PlatformTypeID=62
> AND db_ctx_id=3
> )

In general using a join rather than a sub-query gets better performance:

select *
from table1 t1
, table2 t2
where t1.TargetID = t2.TargetID
and t2.PlatformTypeID = 62
and t2.db_ctx_id = 3

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler AT peppler.org http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html




Archive powered by MHonArc 2.6.24.

Top of Page