Search code examples
sqlmaxinner-joinwinsql

SQL Multiple inner joins with max() for latest recorded entry


Attempting to build SQL with INNER JOIN's. The INNER JOIN's work ok, now I need to add the MAX() function for limiting the rows to just most recent. Added this INNER JOIN client_diagnosis_record ON SELECT cr.PATID, cr.date_of_diagnosis, cr.most_recent_diagnosis... Received this SQL code error, need some help, I'm sure it a simple oversight but my eyes are getting dim from looking so long...

Syntax error: [SQLCODE: <-4>:

SQLCODE: <-4>:<A term expected, beginning with one of the following: identifier, constant, aggregate, %ALPHAUP, %EXACT, %MVR, %SQLSTRING, %

[%msg: < The SELECT list of the subquery


SELECT pd.patient_name,
       cr.PATID,
       cr.date_of_diagnosis,
       cr.EPISODE_NUMBER,
       ce.diagnosing_clinician_value, 
       ce.data_entry_user_name,
       most_recent_diagnosis
FROM client_diagnosis_record cr 
INNER JOIN patient_current_demographics pd ON cr.patid = pd.patid 
INNER JOIN client_diagnosis_entry ce ON ce.patid = pd.patid
                                    AND cr.ID = ce.DiagnosisRecord
INNER JOIN client_diagnosis_record ON (SELECT cr.PATID,
                                              cr.date_of_diagnosis,
                                              cr.most_recent_diagnosis
                                       FROM ( SELECT patid,
                                                     date_of_diagnosis,
                                                     MAX(ID) AS most_recent_diagnosis
                                              FROM client_diagnosis_record) cr
 INNER JOIN RADplus_users ru ON ru.staff_member_id = ce.diagnosing_clinician_code
 WHERE cr.PATID <> '1'
 AND ce.diagnosis_status_value ='Active'
 AND (ru.user_description LIKE '%SOA' OR ru.user_description LIKE '%OA')
 GROUP BY cr.PATID

Solution

  • I tried to re-format you query and it seems your query syntax is not correct. You may try below query -

    SELECT pd.patient_name,
           cr.PATID,
           cr.date_of_diagnosis,
           cr.EPISODE_NUMBER,
           ce.diagnosing_clinician_value, 
           ce.data_entry_user_name,
           most_recent_diagnosis
    FROM client_diagnosis_record cr
    INNER JOIN (SELECT patid,
                       date_of_diagnosis,
                       MAX(ID) AS most_recent_diagnosis
                FROM client_diagnosis_record
                GROUP BY patid,
                         date_of_diagnosis) cr2 ON cr.PATID = cr2.PATID
                                                AND cr.date_of_diagnosis = cr2.date_of_diagnosis
                                                AND cr.ID = cr2.most_recent_diagnosis
    INNER JOIN patient_current_demographics pd ON cr.patid = pd.patid 
    INNER JOIN client_diagnosis_entry ce ON ce.patid = pd.patid
                                        AND cr.ID = ce.DiagnosisRecord
    INNER JOIN RADplus_users ru ON ru.staff_member_id = ce.diagnosing_clinician_code
    WHERE cr.PATID <> '1'
    AND ce.diagnosis_status_value ='Active'
    AND (ru.user_description LIKE '%SOA' OR ru.user_description LIKE '%OA')
    GROUP BY cr.PATID