Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2006
    Posts
    22

    Unanswered: SQL query on Northwind

    Hello

    I usually use a strategy (vision) to solve or construct sql queries in order to get data from database, but i'm afraid that strategy could lead me to wrong sql scripts generating wrong results.

    I discovered that trying to solve the problem described next.

    " Working on Northwind demo database: I want to obtain the total amount of freight paid/defined for orders in which (order details) there are products from supplier '%Pavlova%' ".

    So as i found the correct answer or script, this topic is intended as a kind of survey in order to know if people commit the same error as i did and then, analyzing the problem may develop a "rule" to construct correct sql scripts to avoid that kind of error.

    Then please - what is the sql script you develop to solve the problem descrived?

    And please do not comment anything, just put the script. After getting some replies then I will point to correct answer and ask you for comments regarding my initial mistake.

    And of course try not to look to others code cause it may lead you to write the wrong or right query ;-)

    Thanks a lot for your time on this.


    PD. Attached is the .jpg of necessary part of Northwind model.
    Attached Thumbnails Attached Thumbnails Northwind_Model.JPG  
    Last edited by carloco; 06-05-09 at 11:19.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT SUM(Freight) AS total_freight
      FROM (
           SELECT DISTINCT
                  o.OrderID
             FROM Suppliers AS s
           INNER
             JOIN Products AS p
               ON p.SupplierID = s.SupplierID
           INNER
             JOIN OrderDetails AS od
               ON od.ProductID = p.ProductID
            WHERE s.CompanyName LIKE '%Pavlova%'
           ) AS dt
    INNER
      JOIN Orders AS o
        ON o.OrderID = dt.OrderID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •