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;

Wait, doesn't MS-Access have some special syntax for double joins. — jarlh, Oct 13 '20 at 18:03
Tip of today: Always use modern, explicit `JOIN` syntax everywhere! Easier to write (without errors), easier to read and maintain, and easier to convert to outer join if needed. — jarlh, Oct 13 '20 at 18:04
"does not work" is not a problem description. If everything else is fine, you may simply be missing the parentheses you need for more than 2 JOINs. — Andre, Oct 13 '20 at 18:04
Does this answer your question? [SQL multiple join statement](https://stackoverflow.com/questions/7854969/sql-multiple-join-statement) — Andre, Oct 13 '20 at 18:06

forpas · Accepted Answer · 2020-10-13T18:58:42.600

2

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.

edited Oct 13 '20 at 18:58

answered Oct 13 '20 at 18:33

forpas

160,666
10
38
76

I don't think the data model has a column called FK_Prodcut_ID in the order details table. Also why do you have the subquery select from orders? – Hogan Oct 13 '20 at 18:36
What do you mean by *I don't think the data model has a column called FK_Prodcut_ID in the order details table*? This column is there in the OP's code and it is a foreign key to the table Orders. The subquery is needed in MS Access because it does not support conditions like AND ... IN (...) in the ON caluse. – forpas Oct 13 '20 at 18:40
Thanks! It was really helpful. Also the other queries work, but yours seems more clean. – ianux22 Oct 13 '20 at 19:12
@forpas I don't think it is there because I see the product join to order which joins to orders details -- this is a standard star model -- of course the op did not show use the data model so I can't be sure but it would strange to an orders details table and an orders table both link to the products table. Not to say it never happens -- just I would not expect it. AND if it did then you would not need to join to the orders table at all. – Hogan Oct 13 '20 at 19:29
Again, what do you mean by *it would strange to an orders details table and an orders table both link to the products table*? There is no link of orders to products. – forpas Oct 13 '20 at 19:31

Hogan · Answer 2 · 2020-10-13T18:30:17.343

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.

Your code although it is standard SQL does not work in MS Access. — forpas, Oct 13 '20 at 19:06

score 0 · Answer 3 · answered Oct 13 '20 at 18:46

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 Answers3