OUTER and INNER JOIN Issue

[riaarora]

Hi,



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:

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

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.

Any ides how to show all the 11 ProductName whether we have data or not?

[orange]

see the example of Left Join here for the basics
http://w3schools.com/sql/sql_join_left.asp