SQL-query, optimizing

From: Ingar Eide (ingar.eide_at_daldata.no)
Date: 11/08/04

  • Next message: Hugo Kornelis: "Re: SQL-query, optimizing" 
    Date: Mon, 8 Nov 2004 12:46:45 +0100

    Hi,

    I have some SQL-statements that are very slow, and I'm wondering if I can do
    this in a more efficient way.
    The problem is that I need to list out _detail information_, but the where
    condition check _sum of the group_.

    Here is a simple example of a query (from Northwind):
    I would like to list out all orderlines that are included in orders that
    have a totalamount > 15000.
    The only way I can see to do this is using a Select-statement in the Where
    Clause.

    SELECT C.companyname, O.OrderID, P.ProductName, D.UnitPrice * D.Quantity *
    (1-D.Discount) As Price
    FROM Orders O
    JOIN [Order Details] D ON D.OrderID = O.OrderID
    JOIN Products P ON P.ProductID = D.ProductID
    JOIN Customers C ON C.CustomerID = O.CustomerID
    WHERE EXISTS
    (SELECT 1 FROM [Order Details] A
     WHERE A.OrderID = O.OrderID
     HAVING SUM(A.UnitPrice * A.Quantity * (1-A.Discount)) > 15000)
    ORDER BY C.CompanyName

    Ingar Eide

  • Next message: Hugo Kornelis: "Re: SQL-query, optimizing"