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
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)