如何从两个表（购买和销售）获取项目详细信息 [英] How to get item details from two tables (purchase and sales)

问题描述

我需要一份报告来获取商品详情，即购买和销售该商品



我尝试了什么： < br $>

I need a report to get the item details i.e the purchase and sales done for that item

What I have tried:

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 tblSalesDetails D ON D.ItemCode=A.ItemCode AND D.BatchNo=A.BatchNo
				INNER JOIN tblPurchaseMaster E ON E.PurchaseBill=C.PurchaseBill
				INNER JOIN tblSalesMaster F ON F.InvoiceNo=D.InvoiceNo
				Where A.ItemCode='10'

推荐答案

如果任何表格不包含相应的记录，你的INNER JOINS将不会产生任何结果（即哟你假设每个项目都有销售和购买。



你需要考虑外部加入。



你可能也应该在你的销售和购买细节上做一个UNION作为交易。

Your "INNER JOINS" will yield nothing if ANY table does not contain a corresponding record (i.e. you're assuming every item has "sales" AND "purchases").

You need to consider "OUTER JOINS".

You should probably also do a "UNION" on your sales and purchase details as "transactions".

这是你的查询：

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:

-- INNER JOIN reordered
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.

SELECT（你想要实现的项目）

FROM（选择你想要从中获取任何数据的表，如果表不同，则使用带有别名的innerjoin）（例如：FROM Table_a作为INNERJOINS表_b作为a.empID = b.empID上的b）（内部连接将在数据实现之前发生，并且只有在属性相同时才会发生或彼此链接在不同的表中）

WHERE（无论条件是什么）（例如：WHERE b.empID =5555）

SELECT (what items you want to achieve)
FROM (select the table from which you want to achieve any data from, if tables are different then use innerjoin with alias)(for example: FROM Table_a as a INNERJOINS Table_b as b on a.empID=b.empID)(innerjoin will happen until the data is achieved and will only happen if the attributes are same or linked with one another being in different tables)
WHERE (whatever the condition is) (For example: WHERE b.empID="5555")

这篇关于如何从两个表（购买和销售）获取项目详细信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！