How to convert query to Correlated Query

Question

Struggle with correlated queries and wanted to know if someone could share a better example/explanation how to create a 'Correlated Query.' I do understand the inner/outer queries are dependent upon one another but still do not believe my solution is accurate or feel very sure when coding them and thought of practicing converting queries I have working to correlated. Here is example of working query:

SELECT p.productid, p.productname, SUM(od.unitprice - od.discount) * od.qty
  AS total_amount
  FROM Production.Products AS p
  JOIN Sales.OrderDetails AS od
  ON p.productid = od.productid
  JOIN Sales.Orders AS o
  ON od.orderid = o.orderid
  WHERE o.shipcountry = 'USA'; 

--EDITED--Converted to Correlated Query

So if I wanted to turn this into a correlated sub-query this should be the solution. Thanks for the guidance and advice.

SELECT productid, productname
FROM Production.Products AS t1
WHERE productid = (SELECT SUM(od.unitprice - od.discount) * od.qty AS
total_amount
     FROM Sales.OrderDetails AS od
         JOIN Sales.Orders AS o
     ON od.orderid = o.orderid
     JOIN Production.Products AS t2
     ON t2.productid = t1.productid
     WHERE o.shipcountry = 'USA')
GROUP BY productid, productname;

Thanks for the guidance and advice.

Answer 1

Since you are selecting from both products and orderdetails table, you should use the join based solution only:

select p.productid,
    p.productname,
    SUM((od.unitprice - od.discount) * od.qty)  as total_amount
from Production.Products as p
join Sales.OrderDetails as od on p.productid = od.productid
join Sales.Orders as o on od.orderid = o.orderid
where o.shipcountry = 'USA'
group by p.productid,
    p.productname

Note the added group by clause as you are using sum (also fixed).