How to using join and left join mysql

问题: i want to show my database using join and left join. but look like it not working well. here what i try SELECT ordercustline.QuantityOrdered,ordercustline.ProductID,ord...

问题:

i want to show my database using join and left join. but look like it not working well.

here what i try

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'

the problem is when ordercustline.ProductID have null value then the query will not working, i think it because pdt.LanguageID = 'en'.

when i delete it then the query is working good, but i want to filter the product with pdt.LanguageID = 'en'.

what i have to do to make it work even when ordercustline.ProductID have null value.


回答1:

Put you pdt.LanguageID = 'en' condition in ONClausee instead of 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
  • 发表于 2019-02-19 21:50
  • 阅读 ( 175 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除