I have a table (DIAGNOSIS_DETAIL) which contains the text description of an ICD9 code. I use a LEFT join to the file which contains the diagnosis code so I will keep the record when there is not a corresponding match in the DIAGNOSIS_DETAIL.
My problem is when I don't have a match in the diagnosis detail table. The condition in the WHERE clause deletes the record that has no description. The purpose of the WHERE statement is to eliminate duplicates in this table base on the begin date.
How do I keep all records even when there is not a match in the detail table. I do need the WHERE condition unless there is another way to dedupe the detail table.
LEFT JOIN DIAGNOSIS_DETAIL
ON ( DIAGNOSIS_DETAIL.DIAG_CD = INV_DIAG_COND_OCRNC_DET.INV_DDTL_DIAG_CD )
/* This will give me the current dx description and eliminate dupes */
WHERE "I have a several conditions here and this is one of them"
AND ( DIAGNOSIS_DETAIL.BGN_DTE_CHAR =
(SELECT MAX( CAST( d2.BGN_DTE_CHAR AS DATETIME ) )
FROM DIAGNOSIS_DETAIL d2
WHERE DIAGNOSIS_DETAIL.DIAG_CD = d2.DIAG_CD)
Thanks for your help.
View Complete Post