Mysql Question posted in
The official MySQL documentation can be found here.

Merge multiple joined table rows as one in a query Mysql

I have a DB that stores student records and there are 4 tables that I need to query from to get a student grade report.

The tables being JOIN are school, students, subject, student_score, class

The table structure are as follows:

Table schools
id | name | city_code

Table students
id | firstname |  surname | school_id

Table subjects
id | name | subject_code

Table class
id | name  

Table student_score
id | student_id | subject_id | school_id | class_id | academic_year | academic_term

The expected result is meant to fetches all the schools in a specific city (using city_code) and also all the students in each school and all the students with their respective score for a specific academic year & term. Lastly, each student offers multiple subjects and as such the query fetches all the various score for each subject that was offered by each student.

Currently, this query below fetches all query result based on the desired conditions BUT the student scores are in multiple rows.

SELECT A.name, CONCAT_WS(' ', B.firstname, B.surname) AS fullname, B.regnum AS CAS, D.subject_name,
C.ca_score, C.exam_score FROM schools A
INNER JOIN students B ON A.id = B.school_id 
LEFT JOIN student_score C ON C.student_id = B.id 
LEFT JOIN subjects D ON D.id = C.subject_id  
WHERE A.city_code = 5 AND 
C.academic_term = 'First' AND C.academic_year = '2023' AND C.class_id = 9;

The above query isn’t giving the desired output or result. Each row is meant or expected to be strictly for a specific student and his/her various subject and score are displayed or grouped on that single row based on the student_id.

So, the result I need to fetch from these tables needs to displayed as shown below:

Desired Result structure
school_name | student_name | subject_code_one_ca_score | subject_code_one_exam_score | subject_code_two_ca_score | subject_code_two_exam_score | subject_code_three_ca_score | subject_code_three_exam_score | subject_code_four_ca_score | subject_code_four_exam_score

Below is a sample of the desired result with sample data

Desired Result sample
school_name | student_name | english_ca_score | english_exam_score | maths_ca_score | maths_exam_score | physics_ca_score | physics_exam_score | chemistry_ca_score | chemistry_exam_score

St. Pet Sch | John V. Doe |       20          |         51        |         22      |          49      |        24        |          55        |         22        |          48

Merlin Sch | Jane B. Doe |       19          |         53        |         21      |          50      |        21        |          48        |         24        |          51

Belfast Sch | James P. Doe |       24          |         50        |         18      |          52      |        22        |          50        |         19        |          47

I have attempted using GROUP BY clause to merge the student related data on same row but I get the group clause error

GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Also, I can’t seem to figure how to concat or prepend each subject_code value with the original score column names (ca_score and exam_score)

Tags:

2

Answers


  1. 0

    If you want to run groupBy clause and want to et concated resultant in single row then you will need to remove only_full_group_by from sql_mode.

    Run below query to check sql_mode:
    SHOW VARIABLES LIKE 'sql_mode';
    Output
    Variable_name Value
    sql_mode ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

    In the above variable you will have remove ONLY_FULL_GROUP_BY value.

    Configuration Settings

    To update that variable configuration you can follow this StackoverFlow Thread

    Login or Signup to reply.
  2. 0

    You should avoid using arbitrary letters for table aliases and use abbreviations instead. Something like:

    SELECT sch.name, CONCAT_WS(' ', st.firstname, st.surname) AS fullname, st.regnum AS CAS,
    SUM(IF(sub.subject_code = 'ENGLISH', ss.ca_score, NULL)) AS english_ca_score,
    SUM(IF(sub.subject_code = 'ENGLISH', ss.exam_score, NULL)) AS english_exam_score,
    SUM(IF(sub.subject_code = 'MATHS', ss.ca_score, NULL)) AS maths_ca_score,
    SUM(IF(sub.subject_code = 'MATHS', ss.exam_score, NULL)) AS maths_exam_score,
    ...
FROM schools sch
JOIN students st ON sch.id = st.school_id 
JOIN student_score ss ON ss.student_id = st.id 
JOIN subjects sub ON sub.id = ss.subject_id  
WHERE sch.city_code = 5
AND ss.academic_term = 'First'
AND ss.academic_year = '2023'
AND ss.class_id = 9
GROUP BY sch.id, st.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search