mysql - 如何连接 2 个明细表 - MySql

Question

我有 5 个表命名:

tbl_rawmaterial

tbl_rawmaterialpurchaseorder // master table 1
tbl_rawmaterialpurchaseorderdetail // detail table 1

tbl_rawmaterialpurchase // master table 2
tbl_rawmaterialpurchasedetail // detail table 2

现在tbl_rawmaterial有列:

materialId
materialName
specs
unit

并且tbl_rawmaterialpurchaseorder有列:

poId
... // and more

并且tbl_rawmaterialpurchase有列:

purchaseId
purchaseorderId
... // and more

明细表tbl_rawmaterialpurchaseorderdetail有列:

id
masterId
itemId
qty
rate

详细信息表tbl_rawmaterialpurchasedetail有列:

id
masterId
itemId
batchNo
qty
rate

这是我的查询:

SELECT
dbname.tbl_rawmaterialpurchasedetail.id,
dbname.tbl_rawmaterialpurchasedetail.masterId,
dbname.tbl_rawmaterialpurchasedetail.itemId,
dbname.tbl_rawmaterialpurchasedetail.batchNo,
dbname.tbl_rawmaterialpurchasedetail.qty,
dbname.tbl_rawmaterialpurchasedetail.rate,
dbname.tbl_rawmaterialpurchaseorderdetail.qty AS orderedqty,
dbname.tbl_rawmaterial.specs,
dbname.tbl_rawmaterial.unit
FROM
dbname.tbl_rawmaterialpurchasedetail,
dbname.tbl_rawmaterialpurchase,
dbname.tbl_rawmaterialpurchaseorder,
dbname.tbl_rawmaterialpurchaseorderdetail,
dbname.tbl_rawmaterial
WHERE
dbname.tbl_rawmaterialpurchase.purchaseId = 
dbname.tbl_rawmaterialpurchasedetail.masterId AND
dbname.tbl_rawmaterialpurchaseorder.poId = 
dbname.tbl_rawmaterialpurchaseorderdetail.masterId AND
dbname.tbl_rawmaterial.materialId = 
dbname.tbl_rawmaterialpurchasedetail.itemId AND
dbname.tbl_rawmaterialpurchaseorderdetail.itemId = 
dbname.tbl_rawmaterialpurchasedetail.itemId AND
dbname.tbl_rawmaterialpurchasedetail.masterId = 4 AND
dbname.tbl_rawmaterialpurchaseorder.poId = 
dbname.tbl_rawmaterialpurchase.purchaseorderId

在此之前，表中的数据

tbl_rawmaterialpurchaseorder 和 tbl_rawmaterialpurchaseorderdetail

被复制到

tbl_rawmaterialpurchase 和 tbl_rawmaterialpurchasedetail

我尝试使用join，但结果不同。

我需要帮助将其转换为join

谢谢。

Answer 1

您应该使用内连接。

SELECT
  rmpd.id,
  rmpd.masterId,
  rmpd.itemId,
  rmpd.batchNo,
  rmpd.qty,
  rmpd.rate,
  rmpod.qty AS orderedqty,
  rm.specs,
  rm.unit
FROM dbname.tbl_rawmaterialpurchase rmp 
  INNER JOIN dbname.tbl_rawmaterialpurchasedetail rmpd ON rmp.purchaseId = rmpd.masterId
  INNER JOIN dbname.tbl_rawmaterial rm ON rmpd.itemId = rm.materialId
  INNER JOIN dbname.tbl_rawmaterialpurchaseorder rmpo ON rmp.purchaseorderId = rmpo.poId
  INNER JOIN dbname.tbl_rawmaterialpurchaseorderdetail rmpod ON (rmpo.poId = rmpod.masterId AND rmpod.itemId = rmpd.itemId)
WHERE
  rmpd.masterId = 4 

旁注:最好对表使用别名，以便查询更具可读性。