CODEWITHSUNDEEP
sqlms-access
Brian Michael
Brian Michael

Reputation: 57

Multiple Left Joins in MS Access using sub-queries

I'm trying to run a query with multiple left joins. I understand that MS Access has additional parameters requiring () around the joins, otherwise I get a "Missing Operator" error, however I'm using sub-queries which adds even more complexity. I can't figure out the syntax. Can someone help? Here is my code:

SELECT nPrice.Commodity AS Commodity, nPrice.CodeName AS CodeName, nPrice.
[P/N] AS PartNumber, nPrice.Price AS Today, oPrice.Price AS Tomorrow, 
pPrice.Price AS Next
FROM 
   SELECT * 
   FROM qryPrice 
   WHERE [MyDate] = #9/1/2017# AND [Type] = 'Net Price' AND [Commodity] = 
   'commodityX' 
   ORDER BY CodeName ASC  AS nPrice
   LEFT JOIN SELECT * 
             FROM qryPrice 
             WHERE [MyDate] = #1/1/2018# AND [Type] = 'Net Price' 
             ORDER BY CodeName ASC AS oPrice
             ON nPrice.[P/N] = oPrice.[P/N]
             LEFT JOIN  SELECT *
                        FROM qryPrice
                        WHERE [MyDate] = #2/1/2018# AND [Type] = 'Net Price' 
                        ORDER BY CodeName ASC AS pPrice
                        ON oPrice.[P/N] = pPrice.[P/N]
ORDER BY nPrice.[P/N];

Any help is appreciated. Thanks

Upvotes: 1

Views: 1721

Answers (1)

Parfait
Parfait

Reputation: 107687

While MS Access' dialect requires parentheses around pairs of tables (which can be nested pairs), SQL the language as a general rule (outside of MS Access) requires subqueries to be wrapped in parentheses signifying a contained entity.

Specifically, for your particular JOIN clauses, you only need to wrap the first two derived tables and leave outside without any enclosing outer parentheses. However all subqueries have their own wrapped braces. Below indentation attempts to show parentheses pairings.

Finally, ORDER BY within subqueries are unnecessary as only outer query ordering matters.

SELECT nPrice.Commodity AS Commodity, nPrice.CodeName AS CodeName, 
       nPrice.[P/N] AS PartNumber, nPrice.Price AS Today, oPrice.Price AS Tomorrow, 
       pPrice.Price AS Next
FROM 
   (
    (SELECT * 
     FROM qryPrice 
     WHERE [MyDate] = #9/1/2017# AND [Type] = 'Net Price' AND [Commodity] = 'commodityX' 
    ) AS nPrice

   LEFT JOIN          
     (SELECT * 
      FROM qryPrice 
      WHERE [MyDate] = #1/1/2018# AND [Type] = 'Net Price' 
     ) AS oPrice
   ON nPrice.[P/N] = oPrice.[P/N]
  )

LEFT JOIN  
   (SELECT *
    FROM qryPrice
    WHERE [MyDate] = #2/1/2018# AND [Type] = 'Net Price' 
   ) AS pPrice

ON oPrice.[P/N] = pPrice.[P/N]

ORDER BY nPrice.[CodeName], pPrice.[CodeName], oPrice.[CodeName], nPrice.[P/N];

Upvotes: 4

Related Questions