mysql - Using sub query to group by specific column is outputting the same erroneous result

Question

I asked this question few days ago on SO, but with no valid answer for it.

The problem was that the following query:

SELECT t1.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, :encKey) USING utf8mb4) as patient_name_en,
min(t3.date_of_visit) as date_of_visit, 
t2.diagnosis_name,
max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
ifnull(t5.assessment_result, 0) as assessment_result 
FROM consultation t1
LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
WHERE t2.diagnosis_name LIKE :diagName AND t1.clinic_id = :cid
AND t3.visit_status=:visit_status
GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result
ORDER BY t5.date_of_assessment DESC 

Is giving the following result:

But what I really want is to group by only patient_id so I can get only one row per one patient who was diagnosed earlier with Diabetes.

I searched a lot and found that I can use sub query inside this query.

So I came up with the following sub query:

SELECT t1.patient_id
FROM consultation t1
LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
AND t3.visit_status="Active"
GROUP BY t1.patient_id

The result was grouped by patient_id, and I got the 2 id that really want and not more. Even the patient with ID 0361 was diagnosed with multiple types of diabetes in each visit to the clinic, but it's id is shown once.

Now I want to take this sub query and add it to the initial one:

SELECT t1.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
min(t3.date_of_visit) as date_of_visit, 
t2.diagnosis_name,
max(ifnull(t5.date_of_assessment, 'N/A')) as date_of_assessment,
ifnull(t5.assessment_result, 0) as assessment_result 
FROM consultation t1
LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
WHERE t1.patient_id IN 
(SELECT t1.patient_id
FROM consultation t1
LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id
LEFT JOIN patient t4 ON t4.patient_id = t3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id
WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
AND t3.visit_status="Active"
GROUP BY t1.patient_id) AND
t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
AND t3.visit_status="Active"
GROUP BY t1.patient_id, t5.date_of_assessment, t4.patient_name_en, t3.date_of_visit, t2.diagnosis_name, t5.assessment_result
ORDER BY t5.date_of_assessment DESC 

But I've got the same result of the image above, where per example patient_id=0361 is shown in 4 rows, but I want is to be shown once, with the date of the first time he was diagnoised with Diabetes.

Answer 1

SELECT t.patient_id,
CONVERT(aes_decrypt(t4.patient_name_en, 'key1') USING utf8mb4) as patient_name_en,
mindate as date_of_visit, 
tt2.diagnosis_name,
max(ifnull(tt5.date_of_assessment, 'N/A')) as date_of_assessment,
ifnull(tt5.assessment_result, 0) as assessment_result 
FROM consultation t
LEFT JOIN visit tt3 ON tt3.visit_id = t.visit_id
LEFT JOIN diagnosis tt2 ON t1.diagnosis_id = tt2.diagnosis_id
INNER JOIN
(
    SELECT t1.patient_id,min(t3.date_of_visit) mindate
    FROM consultation t1
    LEFT JOIN diagnosis t2 ON t1.diagnosis_id = t2.diagnosis_id
    LEFT JOIN visit t3 ON t3.visit_id = t1.visit_id 
    WHERE t2.diagnosis_name LIKE '%Diabetes%' AND t1.clinic_id = '361'
    AND t3.visit_status="Active"
    GROUP BY t1.patient_id
)INNERTABLE ON t.patient_id=INNERTABLE.patient_id and INNERTABLE.mindate
LEFT JOIN patient t4 ON t4.patient_id = tt3.patient_id
LEFT JOIN diabetes_assessment t5 ON t5.patient_id = t4.patient_id

Try above query.