mysqlsql-mode

MySQL LEFT OUTER JOIN select best match without changing sql_mode


I'm trying to get a list of students with their best matching subject from subject_inv table. The issue with my query is it requires to change sql_mode. Is there a way to modify this query without changing sql_mode parameter.

    SELECT `student`.*, `subject_inv`.`subject_name`, `score`.`custom_score`, 
    MIN(
    CASE WHEN (`student`.`subject` = `subject_inv`.`subject_name`) THEN 1 WHEN (`student`.`topic1` = `subject_inv`.`subject_name`) THEN 2 WHEN (`student`.`topic2` = `subject_inv`.`subject_name`) THEN 3 WHEN (`student`.`topic3` = `subject_inv`.`subject_name`) THEN 4 END
    ) AS priority 
    FROM `student` 
LEFT OUTER JOIN `subject_inv` ON `subject_inv`.`subject_name`=`student`.`subject` OR `subject_inv`.`subject_name` = `student`.`topic1` OR `subject_inv`.`subject_name` = `student`.`topic2` OR `subject_inv`.`subject_name` = `student`.`topic3` 
LEFT OUTER JOIN `score` ON `student`.`id`=`score`.`id` GROUP BY `student`.`id`, priority

It gives me following error. Probably it should fix when I change the sql_mode from "only_full_group_by" to other.

#1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.student.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Is there a way to get this result without changing sql_mode ?

student
id | full_name | subject | topic1 | topic2 | topic3
___________________________________________________
1  | tom       | sbj1    | sbj4   |        |
2  | sam       | sbj3    | sbj7   |        |
3  | ron       | sbj6    | sbj2   |        |    

subject_inv
id | subject_name | tutor
__________________________
1  | sbj1         | tut1
2  | sbj7         | tut2
3  | sbj4         | tut3
4  | sbj9         | tut3

score
id | custom_score
__________________
1  | 10         
2  | 6         
3  | 9         
4  | 4

expected results:::

id | full_name | subject | topic1 | topic2 | topic3 | subject_name | custom_score
________________________________________________________________________________
1  | tom       | sbj1    | sbj4   |        |        | sbj1         | 10
2  | sam       | sbj3    | sbj7   |        |        | sbj7         | 6
3  | ron       | sbj6    | sbj2   |        |        | NULL         | 9

Solution

  • I would be interested to know how this query performs (and if it produces the expected result) , using coalesce to prioritise. Note I have renamed the tables.

    drop table if exists st,su,sc;
    create table st(id int, full_name varchar(20), subject varchar(20), topic1 varchar(20), topic2 varchar(20), topic3 varchar(20));
    insert into st values
    (1  , 'tom'       , 'sbj1'    , 'sbj4'   , null,null),
    (2  , 'sam'       , 'sbj3'    , 'sbj7'   , null,null),       
    (3  , 'ron'       , 'sbj6'    , 'sbj2'   , null,null);
    
    create table su(id int, subject_name varchar(20), tutor varchar(20));
    insert into su values
    (1  , 'sbj1'         , 'tut1'),
    (2  , 'sbj7'         , 'tut2'),
    (3  , 'sbj4'         , 'tut3'),
    (4  , 'sbj9'         , 'tut3');
    
    create table sc(id int, custom_score int);
    insert into sc values
    (1  , 10),         
    (2  , 6  ),       
    (3  , 9  ),       
    (4  , 4);
    
    select  st.id,st.full_name,st.subject,st.topic1,st.topic2,st.topic3,
            coalesce((select su.subject_name from su where su.subject_name = st.subject) ,
                    (select su.subject_name from su where su.subject_name = st.topic1) ,
                    (select su.subject_name from su where su.subject_name = st.topic2) ,
                    (select su.subject_name from su where su.subject_name = st.topic3)
                    )  subjectname,
             custom_score
    from st
    left join sc on sc.id = st.id;
    
    +------+-----------+---------+--------+--------+--------+-------------+--------------+
    | id   | full_name | subject | topic1 | topic2 | topic3 | subjectname | custom_score |
    +------+-----------+---------+--------+--------+--------+-------------+--------------+
    |    1 | tom       | sbj1    | sbj4   | NULL   | NULL   | sbj1        |           10 |
    |    2 | sam       | sbj3    | sbj7   | NULL   | NULL   | sbj7        |            6 |
    |    3 | ron       | sbj6    | sbj2   | NULL   | NULL   | NULL        |            9 |
    +------+-----------+---------+--------+--------+--------+-------------+--------------+
    3 rows in set (0.02 sec)