由于Null Value数据未重新引用

小编典典

由于Null Value数据未重新引用

sql

我有两个表view_shipment_order_release和order_release_remark。当给定的order_release_gid的order_release_remark中没有记录时，则不会显示任何数据。我想在view_shipment_order_release中显示数据，即使order_release_remark中没有数据也是如此。我怎样才能做到这一点？我的查询如下所示。

select distinct 
  vsor.shipment_gid,
  vsor.order_release_gid,
  orem1.remark_text as Related_Party,
  orem2.remark_text as ULTIMATE_CONSIGNEE_TYPE,
  orem3.remark_text as CONSIGNEE_TYPE
from 
  order_release_remark orem1,
  order_release_remark orem2,
  order_release_remark orem3,
  view_shipment_order_release vsor
--order_release_gid in('GECORP.18460727','GECORP.18435030','GECORP.18439869')
where 
  orem1.REMARK_QUAL_GID ='GECORP.CONSIGNEE TYPE'
  and orem1.order_release_gid=vsor.order_release_gid
  and orem2.REMARK_QUAL_GID ='GECORP.RELATED PARTY' 
  and orem2.order_release_gid=vsor.order_release_gid
  and orem3.REMARK_QUAL_GID ='GECORP.ULTIMATE CONSIGNEE TYPE'
  and orem3.order_release_gid=vsor.order_release_gid
  and vsor.shipment_gid='GECORP.101027274'

;

阅读 163

2021-04-14

共1个答案

小编典典

请改用左联接。即使没有关联方的数据，左联接也不会严格，并且仍会显示。

select distinct 
  vsor.shipment_gid,
  vsor.order_release_gid,
  orem1.remark_text as Related_Party,
  orem2.remark_text as ULTIMATE_CONSIGNEE_TYPE,
  orem3.remark_text as CONSIGNEE_TYPE
from view_shipment_order_release vsor
LEFT JOIN  order_release_remark orem1
ON  orem1.REMARK_QUAL_GID ='GECORP.CONSIGNEE TYPE'
 and orem1.order_release_gid=vsor.order_release_gid
  LEFT JOIN order_release_remark orem2
  ON orem2.REMARK_QUAL_GID ='GECORP.RELATED PARTY'  
  and orem2.order_release_gid=vsor.order_release_gid
  LEFT JOIN order_release_remark orem3 
 ON orem3.REMARK_QUAL_GID ='GECORP.ULTIMATE CONSIGNEE TYPE'
and orem3.order_release_gid=vsor.order_release_gid
where vsor.shipment_gid='GECORP.101027274';

2021-04-14