时间戳

首页 > 解决方案 > Select 语句 where joins 导致记录被排除

sql - Select 语句 where joins 导致记录被排除

问题描述

我有以下带有多个连接的查询。获取 g.fin_id 值需要最后 3 个连接。这工作正常（见结果）但是因为 ACCUM_ISS_CHAR_HIST 表中的一些记录具有 NULL 的 e.char9_nme 值，它完全排除了结果中的记录。因此，似乎当 e.char9_nme 值有记录时，它会产生结果，但是一旦它具有 Null 值，就会被排除在外。我仍然希望看到这些记录，即使这些记录的 g.fin_id 将是空白的，因为它们的 e.char9_nme 值为 Null。如何更改查询以完成此操作？

结果在这里

select 
      a.acct_id, 
      c.fld3_txt, 
      b.issue_loc1_cde,
      b.instr_id, 
      a.fld1_nme, 
      b.issue_cls2_nme, 
      g.fin_id, 
      e.char9_nme 

from position_dg as a
     inner join 
     infoportal..issue_dg as b on b.INSTR_ID = a.INSTR_ID
     inner join 
     InfoPortal..IVW_ACCT as c on a.acct_id = c.acct_id
     inner join 
     InfoPortal..DW_AcctCharDG as d on a.acct_id = d.acctid
     inner join 
     ACCUM_ISS_CHAR_HIST as e on a.instr_id = e.instr_id
     inner join 
     MD_FINANCIAL_ENTITY as f on e.char9_nme = f.fin_enty_name 
     inner join md_FINANCIAL_ENTITY_ALTERNATE_IDENTIFIER as g on 
                f.fin_enty_id = g.fin_enty_id
                and b.MAT_EXP_DTE > getdate()
                and b.issue_cls1_nme = 'Derivatives'
                and a.as_of_tms >= getdate()-1
                and b.iss_typ in ('FFX','IRS','EQF')
                and d.AcctChrSetId = 'DerivativeRpt'
                and d.EndTms IS NULL
                and a.acct_id = 'FOGEMBLCR'
                and g.id_ctxt_typ = 'LEGAL_ENTITY_IDENTIFIER'
                and e.as_of_dte = (
                                   select MAX (as_of_dte)-1 
                                   from accum_iss_char_hist
                                  )

我希望结果会显示一些空白 fin_id 记录的一些 fin_id 记录，但目前只有具有 fin_id 记录的那些是如何显示的，其余的被排除在结果之外。

标签： sqlsql-server

解决方案

您正在寻找左连接。

将所有这些表（如您所说的最后 3 个）作为左连接加入。为了更清楚起见，我在其 ON 子句中移动了每个表的条件，并为基表a创建了 where 子句。

select 
      a.acct_id, 
      c.fld3_txt, 
      b.issue_loc1_cde, 
      b.instr_id, 
      a.fld1_nme, 
      b.issue_cls2_nme, 
      g.fin_id, 
      e.char9_nme 

from  position_dg as a
      inner join
      infoportal..issue_dg as b on b.INSTR_ID = a.INSTR_ID 
                                   and b.MAT_EXP_DTE > getdate()
                                   and b.issue_cls1_nme = 'Derivatives'
                                   and b.iss_typ in ('FFX','IRS','EQF')
      inner join 
      InfoPortal..IVW_ACCT as c on a.acct_id = c.acct_id
      inner join 
      InfoPortal..DW_AcctCharDG as d on a.acct_id = d.acctid
                                     and d.AcctChrSetId = 'DerivativeRpt'
                                     and d.EndTms IS NULL
      left join 
      ACCUM_ISS_CHAR_HIST as e on a.instr_id = e.instr_id 
                               and e.as_of_dte = (
                                         select MAX (as_of_dte)-1 
                                         from accum_iss_char_hist
                                                  )
      left join 
      MD_FINANCIAL_ENTITY as f on e.char9_nme = f.fin_enty_name 
      left join 
      md_FINANCIAL_ENTITY_ALTERNATE_IDENTIFIER as g on f.fin_enty_id = g.fin_enty_id 
                                          and g.id_ctxt_typ = 'LEGAL_ENTITY_IDENTIFIER'
Where a.as_of_tms >= getdate()-1
      and a.acct_id = 'FOGEMBLCR'

推荐阅读