CODEWITHSUNDEEP
t-sqlsql-server-2012
allendks45
allendks45

Reputation: 341

Is this Correlated SubQuery Correct

I am trying to wrap my head around sub-queries (correlated). Although the book explains (MS SQL Server 2012) it I'm still a little confused. Using three tables, Orders, Products and OrderDetails I want to find the sum of all products that were shipped to USA. I came up with the following query but do not quite understand how I got there except the correlation is referencing the 'where' in the outer query. Can someone correct my work and offer better explanation than book? 

SELECT p.productid, p.productname, SUM(qty * od.unitprice) AS TotalAmount
FROM Production.Products AS p
    JOIN Sales.OrderDetails AS od
    ON p.productid = od.productid
    WHERE N'USA' IN 
      (SELECT o.shipcountry 
       FROM Sales.Orders AS o
       JOIN Sales.OrderDetails AS od
       ON o.orderid = od.orderid
       WHERE shipcountry = N'USA')
       GROUP BY p.productid, p.productname;

Upvotes: 1

Views: 34

Answers (1)

Dheebs
Dheebs

Reputation: 408

SELECT
    p.ProductID
,   p.ProductName
,   SUM(qty * od.UnitPrice) as [Total_Amount]
FROM
        Production.Products productid
JOIN        Sales.OrderDetails as od    on p.productid = od.product
JOIN     SalesOrders        as o     on o.OrderID = od.OrderID
WHERE
    o.Shipcountry in ('USA')
Group BY 
    p.ProductID, 
    P.ProductName

The following should work - you technically dont require the sub query. Will only bog down the query.

Upvotes: 1

Related Questions