SQLTeam.com | Weblogs | Forums

Joins causing records to be excluded because of Null value

I have the below query with multiple joins.The last 3 joins are required to get the g.fin_id value. This works fine (see results) BUT because some records in the ACCUM_ISS_CHAR_HIST table have e.char9_nme values of NULL, it excludes the records in the results altogether. So it seems as when the e.char9_nme value has a record then it will produce a result, but as soon as it has a Null value then it is excluded. I would still like to see the records even though the g.fin_id for those will then be blank. How can I change the query to accomplish this?

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)

RESULTS: see attached

Inner join will get only the matching rows.
You need to use left join to see the rows with NULL values.

1 Like