因为空值数据没有反映

2021-12-30 00:00:00 sql oracle11g oracle

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

I have two tables view_shipment_order_release and order_release_remark. When there is no record in order_release_remark for a given order_release_gid, there is no data shown. I want to show data in view_shipment_order_release even if there is no data in order_release_remark. How can I do this? My query is shown below.

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'

;

推荐答案

改用左连接.left join不会严格，即使没有关联方数据也会显示.

Use left join instead. left join will not be strict and will still display even if there is no data on related party.

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';

相关文章