Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2012

    Unanswered: OUTER and INNER JOIN Issue


    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?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    Did you try:
    AND (tbl_Bankers_Mapping.[Team] = "Taiwan" OR tbl_Bankers_Mapping.[Team] Is Null)
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts