LEFT JOIN multiple sub queries

Question

My intention is to create a table with the number of orders and revenue for two different years for every product. My idea was to use a double left Join but I don't understand why this query does not work on MS ACCESS. I decided to use LEFT JOIN because in the subquery per year some products may not appear if it was not ordered. The Subqueries a and b are OK. They work separately. What's the problem with this query?

SELECT p.PK_Product_ID, a.[2013_Orders], a.[2013_Gross_Value], b.[2014_Orders], b.[2014_Gross_Value]
    FROM Products AS p

LEFT JOIN 
     (SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders,  
     SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
     FROM [Order Details] AS od, p, Orders AS o
     WHERE od.FK_Product_ID = p.PK_Product_ID
     AND od.FK_Order_ID = o.PK_Order_ID
     AND YEAR(o.OrderDate) = 2013
     GROUP BY p.PK_Product_ID) AS a

ON p.PK_Product_ID = a.Product_ID

LEFT JOIN 
    (SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2014_Orders,  SUM((p.UnitPrice*od.Quantity)) AS 2014_Gross_Value
    FROM od, p, o
    WHERE od.FK_Product_ID = p.PK_Product_ID
    AND od.FK_Order_ID = o.PK_Order_ID
    AND YEAR(o.OrderDate) = 2014
    GROUP BY p.PK_Product_ID) AS b
 ON p.PK_Product_ID = b.Product_ID;

user_215752 · Answer

This query does not make sense. You say the sub-queries run fine -- but there is no way they can. Try running this query

 SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders,  
 SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
 FROM [Order Details] AS od, p, Orders AS o
 WHERE od.FK_Product_ID = p.PK_Product_ID
 AND od.FK_Order_ID = o.PK_Order_ID
 AND YEAR(o.OrderDate) = 2013
 GROUP BY p.PK_Product_ID

I would expect it to give an error that "p" is not the name of a table.

Maybe you mean this?

 SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders,  
 SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
 FROM [Order Details] AS od, Products as p, Orders AS o
 WHERE od.FK_Product_ID = p.PK_Product_ID
 AND od.FK_Order_ID = o.PK_Order_ID
 AND YEAR(o.OrderDate) = 2013
 GROUP BY p.PK_Product_ID

Which means you probably want something like this:

SELECT p.PK_Product_ID, a.[2013_Orders], a.[2013_Gross_Value], b.[2014_Orders], b.[2014_Gross_Value]
    FROM Products AS p

LEFT JOIN (
   SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders, SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
   FROM [Order Details] AS od
   JOIN Products as p ON od.FK_Product_ID = p.PK_Product_ID 
   JOIN Orders AS o ON od.FK_Order_ID = o.PK_Order_ID AND YEAR(o.OrderDate) = 2013
   GROUP BY p.PK_Product_ID         
) AS a ON p.PK_Product_ID = a.Product_ID

LEFT JOIN (
   SELECT p.PK_Product_ID AS Product_ID, COUNT(o.PK_Order_ID) AS 2013_Orders, SUM((p.UnitPrice*od.Quantity)) AS 2013_Gross_Value
   FROM [Order Details] AS od
   JOIN Products as p ON od.FK_Product_ID = p.PK_Product_ID 
   JOIN Orders AS o ON od.FK_Order_ID = o.PK_Order_ID AND YEAR(o.OrderDate) = 2013
   GROUP BY p.PK_Product_ID         
) AS b ON p.PK_Product_ID = b.Product_ID;

However, I would not bother with two sub-queries -- or even 1 sub-query -- it will just slow you down -- do it like this:

SELECT p.PK_Product_ID, 
  SUM(CASE WHEN COALESCE(o.OrderYear,0) == 2013 THEN 1 ELSE 0 END) as 2013_Orders,
  SUM(CASE WHEN COALESCE(o.OrderYear,0) == 2013 THEN OrderValue ELSE 0 END) as 2013_Gross_Value,

  SUM(CASE WHEN COALESCE(o.OrderYear,0) == 2014 THEN 1 ELSE 0 END) as 2014_Orders,
  SUM(CASE WHEN COALESCE(o.OrderYear,0) == 2014 THEN OrderValue ELSE 0 END) as 2014_Gross_Value,
FROM Products AS p
LEFT JOIN [Order Details] AS od ON od.FK_Product_ID = p.PK_Product_ID 
LEFT JOIN Orders AS o ON od.FK_Order_ID = o.PK_Order_ID
WHERE YEAR(o.OrderDate) in (2013, 2014) -- this is not needed but will may make it faster
GROUP BY p.PK_Product_ID

remember, in SQL you should think in groups -- the first group is orders... the year comes in later.

user_10498828 · Answer

You can do it with conditional aggregation:

SELECT p.PK_Product_ID AS Product_ID,
       SUM(IIF(YEAR(o.OrderDate) = 2013, 1, 0)) AS 2013_Orders,
       SUM(IIF(YEAR(o.OrderDate) = 2013, p.UnitPrice * od.Quantity, 0)) AS 2013_Gross_Value,
       SUM(IIF(YEAR(o.OrderDate) = 2014, 1, 0)) AS 2014_Orders,
       SUM(IIF(YEAR(o.OrderDate) = 2014, p.UnitPrice * od.Quantity, 0)) AS 2014_Gross_Value
FROM (Products AS p LEFT JOIN [Order Details] AS od ON od.FK_Product_ID = p.PK_Product_ID)
LEFT JOIN (SELECT * FROM Orders WHERE YEAR(OrderDate) IN (2013, 2014)) AS o ON od.FK_Order_ID = o.PK_Order_ID
GROUP BY p.PK_Product_ID

If you want only the products ordered in the years 2013 and/or 2014 then you can change the LEFT joins to INNER joins.

user_74195 · Answer

Why don't you run via a single query manual cross-tab style. I am querying the orders table only for records within the date range of Jan 1, 2013 to LESS THAN Jan 1, 2015. This gives me all orders across the 2 years regardless of an item begin sold or not in one year or the other.

Now, I am doing a sum() based on an IIF(). If the year of the order is 2013, it applies a 1 or 0 respectively to their totals. Similarly for 2014. These should be obvious per the construct and the final column name. If the record is not applicable to the year, its simply zero. Same for the gross value, I just happen to multiply times 1 or 0 as applicable.

The only thing that does not make sense is getting the unit price from the product table. I would actually have thought there would be a price at the order detail level as prices obviously fluctuate over the years, and I'll be the prices from 2013 and 2014 respectively are nothing to the prices within the current database of 2020 rates. So, that probably needs to be properly adjusted.

SELECT 
        od.FK_Product_ID,
        sum( iif( year( o.orderDate ) = 2013, 1, 0 )) Orders_2013,
        sum( iif( year( o.orderDate ) = 2014, 1, 0 )) Orders_2014,
        sum( iif( year( o.orderDate ) = 2013, 1, 0 ) * p.UnitPrice * od.Quantity ) GrossValue_2013,
        sum( iif( year( o.orderDate ) = 2014, 1, 0 ) * p.UnitPrice * od.Quantity ) GrossValue_2014
    FROM 
        Orders AS o
            JOIN [Order Details] AS od
                ON o.PK_Order_ID = od.FK_Order_ID
                JOIN [Products] as p
                    on od.FK_Product_ID = p.PK_Product_ID
    WHERE 
            o.OrderDate >= #1/1/2013#
        AND o.OrderDate < #1/1/2015#
    GROUP BY 
        od.FK_Product_ID

LEFT JOIN multiple sub queries

3 Answers