My query is not returning correct result of outer join I tried to write query in different way but no success.
Please find below my query:
I have 11 tbl_Product_Categories.ProductName but do not have data in tbl_Consol_Weekly_Product for all 11 ProductName but I want to print all the 11 ProductName. If I remove the condition tbl_Bankers_Mapping.[Team] = "Taiwan" then it works but the movment I add tbl_Bankers_Mapping.[Team] = "Taiwan" in the condition I'm getting only those ProductName which have data in table.
SELECT ProdCat.ProductName, Sum(tbl_Consol_Weekly_Product.RevenueInUSD) AS Revenue FROM tbl_Consol_Weekly_Product, tbl_Bankers_Mapping,
(SELECT tbl_Product_Categories.ProductName FROM tbl_Consol_Weekly_Product RIGHT JOIN tbl_Product_Categories ON tbl_Consol_Weekly_Product.[Report Type] = tbl_Product_Categories.ProductName GROUP BY tbl_Product_Categories.ProductName) AS ProdCat
WHERE tbl_Consol_Weekly_Product.[Private Banker] = tbl_Bankers_Mapping.[GMIS Revenue Producer]
AND tbl_Consol_Weekly_Product.[Report Type] = ProdCat.ProductName
AND tbl_Bankers_Mapping.[Team] = "Taiwan"
GROUP BY ProdCat.ProductName
Any ides how to show all the 11 ProductName whether we have data or not?