CODEWITHSUNDEEP
sql-servert-sqlcorrelated-subquery
allendks45
allendks45

Reputation: 341

How to convert query to Correlated Query

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.

Upvotes: 0

Views: 829

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39487

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).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think this is what you want:

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

A correlated subquery is not appropriate here. You are returning results from multiple tables. Just do the JOINs correctly with a GROUP By.

Upvotes: 1

Related Questions