SQL Problem : Query

Question

I have an issue in query. I have tables product, stockRecord and priceDetail. I want to display all products. If the price is not defined in priceDetail for that product, then it will be 0.00; similarly if the quantity is not defined in stockRecord table, then the quantity should be 0.

But if the price is defined in the priceDetail table, then we should get the latest price from the table

WMProduct

  BusinessUnit   ProductCode    Description    SalableFlag
  MASS           0001           Pen            1
  MASS           0002           Computer       1
  MASS           0003           Book           1
  MASS           0004           Bottle         1

WMStockRecord

 ProductCode     AvailableQuantity
 0001            10
 0003            15

WMPriceDetail

 ProductCode   DateFrom      DateTo        Price
 0001          10-10-2009    10-10-2011    100
 0001          10-12-2009    10-10-2010    80
 0001          12-12-2010    01-12-2011    120
 0002          12-01-2010    ''            200
 0004          12-12-2010    12-05-2011    100

I need list of products like this:

BusinessUnit  ProductCode   Description SalableFlag   Price    AvailableQuantity
MASS          0001          Pen         1             120      10
MASS          0002          Computer    1             200      0
MASS          0003          Book        1             0.00     15
MASS          0004          Bottle      1             0.00     0

Answer 1

Try using sub query and left join like below :

SELECT P.ProductCode AS ProductCode, 
       P.Description AS ProductName,  
       P.SalableFlag AS Salable,
       ISNULL(STK.AvailableQuantity, 0) AS Qty,
       ISNULL((SELECT TOP 1 Price FROM WMPriceDetail
               WHERE ProductCode = P.ProductCode ORDER BY DateTo DESC), 0) AS Price 
  FROM WMProduct P
  LEFT JOIN WMStockRecord STK ON P.ProductCode = STK.ProductCode