MySQL query giving erroneous results because of the mode of only full group by

Question

I have this unanswered question where I had this erroneous result 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,
    t2.diagnosis_name,
    t3.date_of_visit
    t4.patient_name_en,
    t5.date_of_assessment
    t5.assessment_result
ORDER BY t5.date_of_assessment DESC 

Which gives me this result:

The new thing is that I tried the following query using sub query to loop through patient_id:

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 

And I've got the same result shown at the image above.

Here is the an sql fiddle about my problem

What I really want is to show only the following two rows which means group by patient_id.

And for few reasons, I cannot disable the only_full_group_by mode,because I read it may give some faulty result.

I tried to only group by patient_id in the fiddle and apparently it works properly because the fiddle has the only_full_group_by disabled.

What should I do ?

1. Create a PHP solution where I break the query into 2, the first one is the sub query where I will get the IDs and then use the outer query in a foreach to get a row for each ID using LIMIT 1 ?
2. Or disable the only_full_group_by mode which will affect my other queries in my application on different aspects ?
3. Create an sql stored procedure instead of the solution #1 ?

Answer 1

The columns in the GROUP BY have to be the columns being returned. So instead of t5.assessment_resultit should just beassessment_resultbecause this is the alias of theIFNULL()` expression.

Also, you shouldn't include the results of aggregation functions in GROUP BY, so don't use t5.date_of_assessment or t3.date_of_visit there.

Answer 2

Try to use group_concat(column_name separator ', '). group_concat() doc

It will help concatenate rows based on youd desired group