Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
HI,
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.
Encounters:
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,
a.BMI, a.BP_SYSTOLIC, a.BP_DIASTOLIC, e.DEPARTMENT_NAME,
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:
Patient:
LOAD *;
left join (Encounters)
sql
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;
Left Join and Left Keep leave all the records from the left table. In your case that means all the records of the Encounters table.
In addition a Left Join can create more records if for a single record in the left table there are several matching records in the right table.
Table1:
LOAD * INLINE [
A, B
1, 1
2, 2
];
JOIN (Table1)
LOAD * INLINE [
A, C
1, 10
1, 20
2, 30
];
The Result is a table with these records:
A, B, C
1, 1, 10
1, 1, 20
2, 2, 30
Table1 had two records before the left join and has three records after the left join.
Hi Gysbert,
I know how JOIN AND KEEP work, but it does not work for me.
My problem is that ALL the records are fetched for the right table (Patients). The left table is the encounter table which has multiple records for one patient (right table). I wanted to get patient list from encounters. Logically it might cause the problem. Please review my code to see whether your can help. Thanks
It's very simple. If all the records from the right side table are joined to the left table then all the keys from the right side table exist in the left side table. Check your data. I can't do that for you.
I checked the data. There are only 42,818 encounters (left table), but the right table (patient) returned almost 4 million distinct patient records. Something is not right!
As per your comments, apply some filters and reduce the rows to less than 100 digits on the first query and check the result.
The other test, join directly on SQL and check how one single query returning ?
I can test more with a smaller size of data. Is the syntax ok? It does not make sense if all the records in right table are returned.