How To Write A Query To Return Null For Non-exist Record In An Outer Join.

I have two tables:





1. tblProducts


columns: ProductID, ProductName





2. tblProductCodes


columns: ProductID, CustomerID, ProductCode





The 2nd table is for storing product codes for customers, in other words, one product can have different ProductCode for different customers. But some customers do not have ProductCode for a ProductID.





I want to create a query to return all the Products and its ProductCode (null is valid) for a specific customer.





I tried:





SELECT dbo.tblProductCodes.ProductCode, dbo.tblProductCodes.CustomerID,


dbo.tblProducts.ProductName,


dbo.tblProducts.ProductID


FROM dbo.tblProducts LEFT OUTER JOIN


dbo.tblProductCodes ON dbo.tblProducts.ProductID = dbo.tblProductCodes.ProductID


WHERE dbo.tblProductCodes.CustomerID = 2





But the query left out all products that does not have ProductCode value in tblProductCodes table for CustomerID = 2. I want all the ProductName returned from query and render null or empty string for ProductCode value if the code does not exist in tblProductCodes table for the customer.





Any help is highly appreciated.