6 Replies Latest reply: Feb 2, 2016 3:17 PM by haijunw wang RSS

    LEFT JOIN (or LEFT KEEP) returns too many records

    haijunw wang


      I read this post (Understanding Join, Keep and Concatenate) and thought it it easy to get the records by using LEFT JOIN or LEFT KEEP. It turned out that it is not easy. The LEFT JOIN (or KEEP) returned all the records including the records does not match.  What is my problem?


      I this one in first script tab.


      with DiabetesPatientList as (     

      select distinct a.pat_id

      from problem_list a          

      where a.icd9_code like '250.%'


      select a.pat_id, a.PAT_ENC_CSN_ID,a.Contact_date, a.PCP_PROV_ID, a.FIN_CLASS_C,  a.VISIT_PROV_ID, a.VISIT_PROV_TITLE,


      trunc ((a.contact_date - b.birth_date)/365.25) as "EncounterAge",

      c.Prov_name as PCP,

      d.prov_name as VisitPhysician,

      e.Department_NAME as Department´╝î

      extract(Year from a.contact_date) as intYear, extract(Year from a.contact_date)*100+extract(month from a.contact_date) as IntYearMonth

      from pat_enc a 

      inner join patient b on a.pat_id = b.pat_id

      INNER JOIN DiabetesPatientList f on f.pat_id=a.pat_id

      LEFT JOIN clarity_SER c on a.PCP_PROV_ID =c.prov_id    

      left join clarity_ser d on d.prov_id = a.visit_prov_id

      left join clarity_dep e on a.DEPARTMENT_ID = e.DEPARTMENT_ID       

      where a.CONTACT_DATE >= to_date('01-Jan-2011', 'dd-mon-yy') and      

      (a.DEPARTMENT_ID in (1711521800,1710021800)

      and a.APPT_STATUS_C=2

      and a.ENC_TYPE_C =101;


      And the script in the second tab:


      LOAD *;

      left join (Encounters)


      select  a.pat_id, a.PAT_NAME, a.PAT_MRN_ID, a.Birth_Date, a.CUR_PCP_PROV_ID, g.name as Sex

      from patient a

      left join ZC_SEX g on g.RCPT_MEM_SEX_C=a.sex_c;