How To Return Null Values For Non-exist Record On A Left Join Statement

I have table Products and Orders that has the following columns:





table Products: ProductID, ProductName


table Orders: OrderID, ProductID, OrderDate, Quantity, Price





The Orders table contains orders placed on all the dates. I want to obtain a list of orders for a particular date, if there is no order for a product on the requested date, I want to return null values for the Quantity and Price fields.





I tried the following select statement:





select Products.ProductName, Orders.Quantity, Orders.Price from Products left join Orders on Products.ProductID = Orders.ProductID where Orders.OrderDate = '10/16/2004'








Where, there are a total of three products (A,B,C) in table Products. Product-C has no order on 10/16/2004, but I want it to return :





ProductName / Quantity / Price


Product-A 5 1.89


Product-B 6 2.43


Product-C null null





Obviously, my sql statement won't work becaue the where clause will filter out Product-C.





Could anyone help me figure out how to modify my sql code to get the resultset I want?





Thanks in advance.