I have a list- null, 1,2,3 and am asking for only the 1 but keep getting 1 and null.

[snelson476]

In a subquery I only want the line 1 from a list of null, 1,2 and 3, but I keep getting line 1 and null. How can I get only line 1?

  (select HOSPICE_DX.PAT_ENC_CSN_ID
           , edg.DX_NAME
           , HOSPICE_DX.LINE
            from HOSPICE_DX
           inner join EDG on    HOSPICE_DX.HOSPICE_DX_CODED_ID=edg.DX_ID
         
           ) hospice on PAT_ENC.PAT_ENC_CSN_ID=hospice.PAT_ENC_CSN_ID and hospice.LINE=1

thanks!

[Rabbit]

We need to see the rest of the SQL.

[snelson476]

select PATIENT.PAT_MRN_ID as MRN
, PATIENT.PAT_NAME as Patient
, convert(date,HH_EPSD_EVNT_DATES.DATE_OF_EVENT,101) as SOC
, CASE when ZC_ACCT_CLASS_HA.NAME='Home Health' then diagnosis.DX_NAME else hospice.DX_NAME end as 'PRIMARY DX'
, ZC_TEAM_ASSIGNMENT.NAME as TEAM
, ZC_ACCT_CLASS_HA.NAME AS PROGRAM
, hospice.LINE



from HSP_ACCOUNT
inner join ZC_ACCT_CLASS_HA on HSP_ACCOUNT.ACCT_CLASS_HA_C=ZC_ACCT_CLASS_HA.ACCT_CLASS_HA_C
inner join HSP_ACCOUNT_3 on HSP_ACCOUNT.HSP_ACCOUNT_ID=HSP_ACCOUNT_3.HSP_ACCOUNT_ID
inner join HSP_TRANSACTIONS on HSP_ACCOUNT.HSP_ACCOUNT_ID=HSP_TRANSACTIONS.HSP_ACCOUNT_ID
inner join PATIENT on HSP_ACCOUNT.PAT_ID=PATIENT.PAT_ID
inner join PAT_ENC on HSP_ACCOUNT.HSP_ACCOUNT_ID=PAT_ENC.HSP_ACCOUNT_ID
inner join EPISODE on HSP_ACCOUNT_3.HH_HSB_ID=EPISODE.EPISODE_ID
inner join HH_EPSD_INFO on EPISODE.EPISODE_ID=HH_EPSD_INFO.SUMMARY_BLOCK_ID
left join ZC_TEAM_ASSIGNMENT on HH_EPSD_INFO.TEAM_ASSIGNMENT_C=ZC_TEAM_ASSIGNMENT.TEAM_ASSIGNMENT_C
inner join HH_EPSD_EVNT_DATES on HH_EPSD_INFO.SUMMARY_BLOCK_ID=HH_EPSD_EVNT_DATES.SUMMARY_BLOCK_ID
inner join ZC_DATE_EVENT on HH_EPSD_EVNT_DATES.DATE_EVENT_C=ZC_DATE_EVENT.DATE_EVENT_C
inner join HH_EPSD_EVNT_DATES as ed on HH_EPSD_INFO.SUMMARY_BLOCK_ID=ed.SUMMARY_BLOCK_ID
inner join ZC_MC_PAT_STATUS on HH_EPSD_INFO.HC_DC_DISPOSITION_C=ZC_MC_PAT_STATUS.PAT_STATUS_C
inner join CLARITY_DEP on HSP_TRANSACTIONS.DEPARTMENT=CLARITY_DEP.DEPARTMENT_ID
left join (select HH_PAT_ENC.PAT_ID
            , ROW_NUMBER() over(partition by hh_pat_enc.pat_id order by hh_pat_enc.prim_dx_start_date desc)enc_rank
            , CLARITY_EDG.DX_NAME
             from HH_PAT_ENC
              inner join CLARITY_EDG on HH_PAT_ENC.PRIMARY_DX_ID=CLARITY_EDG.DX_ID
               ) diagnosis on PATIENT.PAT_ID=diagnosis.PAT_ID and diagnosis.enc_rank='1'
left join (select HOSPICE_DX.PAT_ENC_CSN_ID
           , HOSPICE_DX.CONTACT_DATE
           , edg.DX_NAME
           , HOSPICE_DX.LINE
            from HOSPICE_DX
           inner join CLARITY_EDG as edg on HOSPICE_DX.HOSPICE_DX_CODED_ID=edg.DX_ID
         
           ) hospice on PAT_ENC.PAT_ENC_CSN_ID=hospice.PAT_ENC_CSN_ID and hospice.LINE=1 
           

where ZC_DATE_EVENT.NAME in ('Start of Care Date (M0030,CMS 485.2)', 'Hospice Admission')
and ZC_MC_PAT_STATUS.NAME <>  'Left Against Medical Advice or Discontinued Care'
and EPISODE.STATUS_C<>'3'





group by EPISODE.EPISODE_ID
, PATIENT.PAT_MRN_ID 
, PATIENT.PAT_NAME
, HH_EPSD_EVNT_DATES.DATE_OF_EVENT
, ZC_TEAM_ASSIGNMENT.NAME 
, ZC_ACCT_CLASS_HA.NAME
, hospice.DX_NAME 
, hospice.LINE
, diagnosis.DX_NAME 


order by 1

[Rabbit]

It's because you did a left join. A left join returns any rows in the left table that match the right table in addition to any rows in the left table that don't match any rows in the right table.