Hi everyone.
I have a stored procedure which queries data from three tables named StockBfwd, ReceiptMonthly, IssueMonthly. it works very well only that when one table does not have the information that relates to the two, then no results are thrown. the stored procedure is shown here below.
SELECT DISTINCT Stock.StockId, Stock.PartNo, Stock.ItemName, Stock.Packaging1, Stock.Packaging2, Stock.Bin, StockBfwd.StockBfwd AS Bfwd,
Stock.UnitCost, Stock.UnitSell, Stock.SubCategoryId, SubCategory.SubCategoryName,
(SELECT SUM(ReceiptMonthly.StockIn) FROM ReceiptMonthly WHERE ReceiptMonthly.StockId=Stock.StockId) AS StockIn,
(SELECT SUM(IssueMonthly.StockOut) FROM IssueMonthly WHERE IssueMonthly.StockId=Stock.StockId) AS StockOut
FROM Stock LEFT OUTER JOIN
StockBfwd ON Stock.StockId = StockBfwd.StockID INNER JOIN
ReceiptMonthly ON Stock.StockId = ReceiptMonthly.StockId INNER JOIN
IssueMonthly ON Stock.StockId = IssueMonthly.StockId INNER JOIN
SubCategory ON Stock.SubCategoryId = SubCategory.SubCategoryId;
Thanks