you can try these statement ::

SELECT Orders.productid , Product.ProductName , Location1.OrderedQty , Location2.OrderedQty , Location3.OrderedQty

FROM Orders INNER JOIN Product ON Orders.ProductID = Product.ProductID

LEFT OUTER JOIN

(

SELECT productid ,...