Passing Data Through A Three Tiered Scalar Subquery

Question

I have a query, that has three tiers. That is to say, I have a main query, which has a scalar subquery, which also contains a scalar subquery.

The bottom level scalar query is returning two different values two which the mid-level subquery is returning an average of. However, instead of the bottom level query receiving the current value, it is averaging ALL of the values in the table.

Does anyone know how to properly pass the value of the current top level query result to the bottom subquery?

Code Example:

Select Product, 
       Description, 
       (Select Avg(Mfg_Cost, Purchasing_Cost) 
        FROM (Select Mfg_Cost, 
                     Purchasing Cost
              From Cost
              Where Cost.Product = Products.Product)) 
From Products;

StuartLC · Answer 1 · 2012-04-09T14:09:09.513

Can't you just use a JOIN and GROUP BY, like so:

Select p.Product, 
       p.Description, 
       Avg(c.Mfg_Cost),
       Avg(c.Purchasing_Cost)
From Products p 
    INNER JOIN
              Cost c
              ON c.Product = p.Product
GROUP BY p.Product, p.Description;

In general, if you need to return more than one value from a subquery:

Select p.Product, 
       p.Description,
       q2.AvgMfg_Cost,
       q2.AvgPurchasing_Cost
From Products p INNER JOIN
  (
       Select c.Product, 
              Avg(c.Mfg_Cost) AS AvgMfg_Cost, 
              Avg(c.Purchasing_Cost) AS AvgPurchasing_Cost
              From Cost c
              Group by c.Product
  ) AS q2
  on q2.Product = p.Product;

In Microsoft SQL Server, you can also use a Common Table Expression (CTE)

Thanks nonnb, unfortunately my situation is quite a bit more complex than I initially led on. I have since dug some more and found my problem. I had no way of passing the value from the top query to the bottom subquery. I fixed it and all is right with the world again! Thanks! — enrique41, Apr 09 '12 at 15:04

Passing Data Through A Three Tiered Scalar Subquery

1 Answers1