Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Subquery efficiency


I'm a bit puzzled how I can make a subquery more efficient. Here is a simple
example of what I'm after:

SELECT em.Name, tr.DistanceTraveled
FROM Employee em,
     (SELECT SUM(distance) AS DistanceTraveled, EmployeeID FROM Trip GROUP
BY EmployeeID) tr
WHERE em.ID = 123 AND
      tr.EmployeeID = em.ID

(sorry i made this up quickly as my example is vastly more complex so excuse
any mistakes)

Here I think the the subquery does the SUM(distance) for each and every
employee rather than just the one i'm interested in. So really, what I
wanted was something like this:

SELECT em.Name, tr.DistanceTraveled
FROM Employee em,
     (SELECT SUM(distance) AS DistanceTraveled, EmployeeID FROM Trip
      WHERE EmployeeID = em.ID
      GROUP BY EmployeeID) tr
WHERE em.ID = 123

but this doesn't work.

Any hints are greatly appreciated!

Pete

Untested...

SELECT em.Name, tr.DistanceTraveled
FROM Employee em
INNER JOIN
    (SELECT SUM(distance) AS DistanceTraveled, EmployeeID
        FROM Trip
        GROUP BY EmployeeID) tr
    ON tr.employeeid = em.ID
WHERE em.ID = 123

If you are using SQL 2005...

;WITH tr AS
    (SELECT SUM(distance) AS DistanceTraveled, EmployeeID
        FROM Trip
        GROUP BY EmployeeID)
SELECT em.Name, tr.DistanceTraveled
FROM Employee em
    INNER JOIN tr ON tr.employeeid = em.ID
WHERE em.ID = 123

--Peter

--
Peter DeBetta, MVP - SQL Server
http://sqlblog.com
--

"Peter" <peteATkapiti.co.nz> wrote in message

news:esz07OxoHHA.4900@TK2MSFTNGP05.phx.gbl...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc