Denis Denis - 7 months ago 11
SQL Question

LEFT JOIN on two tables

I tried the following SQL query to load data from a database to PowerPivot model based on instructions from a PowerPivot manual:

SELECT
Production_Product.Name AS Product,
Production_ProductCategory.Name AS Category,
Production_ProductSubcategory.Name AS SubCategory
FROM
Production_Product
LEFT OUTER JOIN Production_ProductSubcategory
ON Production_Product.ProductSubcategoryID = Production_ProductSubcategory.ProductSubcategoryID
LEFT OUTER JOIN Production_ProductCategory
ON Production_ProductSubcategory.ProductCategoryID = Production_ProductCategory.ProductCategoryID


Unfortunatelly it keeps throwing a syntax error (missing operator) and I can't find what went wrong. It worked perfectly with only one (first) inner join.

Answer

Since you have not specified the RDBMS I would assume it is MS Access

Because the syntax of your query is standard SQL and is correct for all RDBMS but MS access, as you must surround several LEFT JOINs with parenthesis in Access

SELECT
  Production_Product.Name AS Product,
  Production_ProductCategory.Name AS Category,
  Production_ProductSubcategory.Name AS SubCategory
FROM
  ((Production_Product
  LEFT OUTER JOIN Production_ProductSubcategory
    ON Production_Product.ProductSubcategoryID = Production_ProductSubcategory.ProductSubcategoryID)
  LEFT OUTER JOIN Production_ProductCategory
    ON Production_ProductSubcategory.ProductCategoryID = Production_ProductCategory.ProductCategoryID)