如何使用join和left join mysql

Question

我想使用join和left join来显示我的数据库。但看起来效果不太好。

以下是我尝试的内容

SELECT ordercustline.QuantityOrdered,ordercustline.ProductID,ordercustline.UnitCustPrice,ordercustline.QuantityDelivered,ordercustline.Active,ordercustline.ProductNumber,products.ProductNumber as PN,products.id,pdt.ProductName,dbpjs.CodeName,ordercustline.id as oid, pdt.LanguageID 
  FROM ordercustline 
  LEFT JOIN products ON products.id = ordercustline.ProductID 
  LEFT JOIN producttexts as pdt ON pdt.ProductID = products.id 
  JOIN DiagnosisBPJS as dbpjs ON dbpjs.id = ordercustline.ProductNumber 
  WHERE ordercustline.OrderID = 20 AND ordercustline.Active = 2 AND pdt.LanguageID = 'en'

问题是当ordercustline.ProductID具有null值时，查询将无法工作，我认为这是因为pdt.LanguageID = 'en'。

当我删除它时，查询工作正常，但我想用pdt.LanguageID = 'en'过滤产品。

即使ordercustline.ProductID有null值，我也要做些什么才能让它正常工作。

Answer 1

将pdt.LanguageID = 'en'条件放在ONClausee中，而不是Where clause中

SELECT ordercustline.QuantityOrdered,ordercustline.ProductID,ordercustline.UnitCustPrice,ordercustline.QuantityDelivered,ordercustline.Active,ordercustline.ProductNumber,products.ProductNumber as PN,products.id,pdt.ProductName,dbpjs.CodeName,ordercustline.id as oid, pdt.LanguageID 
  FROM ordercustline 
  LEFT JOIN products ON products.id = ordercustline.ProductID 
  LEFT JOIN producttexts as pdt ON pdt.ProductID = products.id AND pdt.LanguageID = 'en'
  JOIN DiagnosisBPJS as dbpjs ON dbpjs.id = ordercustline.ProductNumber 
  WHERE ordercustline.OrderID = 20 AND ordercustline.Active = 2