This is your query:
SELECT
A.ItemCode
, A.ItemName
, A.Barcode
, A.Unit
, A.PurchaseRate
, A.SellingRate
, A.DiscountPercent
, A.OpeningStock
, A.BatchNo
, A.ExpiryDate AS ExpDate
, B.Quantity AS StockQuantity
, E.PurchaseDate
, F.InvoiceDate
FROM
tblItemMaster A
INNER JOIN tblStockMaster B ON B.ItemCode = A.ItemCode AND B.BatchNo=A.BatchNo
INNER JOIN tblPurchaseDetails C ON C.ItemCode = A.ItemCode AND C.BatchNo=A.BatchNo
INNER JOIN tblPurchaseMaster E ON E.PurchaseBill = C.PurchaseBill
INNER JOIN tblSalesDetails D ON D.ItemCode = A.ItemCode AND D.BatchNo=A.BatchNo
INNER JOIN tblSalesMaster F ON F.InvoiceNo = D.InvoiceNo
WHERE
A.ItemCode='10'
Query seems alright. The query will result fine as long as you have a sails record for that item. If you want to see results even if no sales exists. Then rewrite as follow:
SELECT
A.ItemCode
, A.ItemName
, A.Barcode
, A.Unit
, A.PurchaseRate
, A.SellingRate
, A.DiscountPercent
, A.OpeningStock
, A.BatchNo
, A.ExpiryDate AS ExpDate
, B.Quantity AS StockQuantity
, E.PurchaseDate
, F.InvoiceDate
FROM
tblItemMaster A
INNER JOIN tblStockMaster B ON B.ItemCode = A.ItemCode AND B.BatchNo=A.BatchNo
INNER JOIN tblPurchaseDetails C ON C.ItemCode = A.ItemCode AND C.BatchNo=A.BatchNo
INNER JOIN tblPurchaseMaster E ON E.PurchaseBill = C.PurchaseBill
LEFT OUTER JOIN tblSalesDetails D ON D.ItemCode = A.ItemCode AND D.BatchNo=A.BatchNo
LEFT OUTER JOIN tblSalesMaster F ON F.InvoiceNo = D.InvoiceNo
WHERE
A.ItemCode='10'
There is one issue though. What would happen if item was returned and resold? Since I do not know how you manage this. I can't suggest anything.