I have the following query SELECT Item.No_, Item.Description, Table1.Inventory, Table2.[Qty on Sales Order], Table3.[Qty on Blanket Order] FROM [CompanyName$Item] AS Item LEFT OUTER JOIN (SELECT [Item No_], ISNULL(SUM(Quantity), 0) AS Inventory FROM [CompanyName$Item Ledger Entry] AS ILE GROUP BY [Item No_]) AS Table1 ON Item.No_ = Table1.[Item No_] LEFT OUTER JOIN (SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Sales Order] FROM [CompanyName$Sales Line] AS SL GROUP BY No_, [Document Type], Type) AS Table2 ON Item.No_ = Table2.No_ AND Table2.[Document Type] = 1 AND Table2.Type = 2 LEFT OUTER JOIN (SELECT No_, [Document Type], Type, ISNULL(SUM([Outstanding Quantity]), 0) AS [Qty on Blanket Order] FROM [CompanyName$Sales Line] AS BSL GROUP BY No_, [Document Type], Type) AS Table3 ON Item.No_ = Table3.No_ AND Table3.[Document Type] = 4 AND Table3.Type = 2To get only those records from Item Table Where [Qty on Blanket Order] <> 0 OR [Qty on Sales Order] Where should i put this filter.
I assume you mean, "where Qty_on_blanket_order <> 0 or Qty_on_sales_order <> 0", right? Since you must allow for either aggregate value to equal 0, you cannot filter within the derived tables. At the end of the main query, simply add that WHERE clause.