MySQL LEFT OUTER JOIN select best match without changing sql_mode

Question

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

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 — Dumindu Madushanka, Aug 19 '18 at 06:07

ScaisEdge · Answer 1 · 2018-08-19T06:52:12.050

1

you could use a inner join with the subselect for MIN eg:

 SELECT distinct  `student`.*, `subject_inv`.`subject_name`, `score`.`custom_score`
 , t.priority 
  FROM `student` 
  INNER JOIN (

    select  `student`.`id`, 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`
    group by `student`.`id`
  ) t on t.`id` = `student`.`id`
  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`

but for sql_mode=only_full_group_by
if you need outhers column that a not in group by you should use aggreagtion function too

 SELECT  `student`.*
 , min(`subject_inv`.`subject_name`)
 , min(`score`.`custom_score`)
 , t.priority 
  FROM `student` 
  INNER JOIN (

    select  `student`.`id`, 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`
    group by `student`.`id`
  ) t on t.`id` = `student`.`id`
  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`, t.priority

edited Aug 19 '18 at 06:52

answered Aug 19 '18 at 06:25

ScaisEdge

131,976
10
91
107

Still the same issue. – Dumindu Madushanka Aug 19 '18 at 06:39
answer updated .. there is not the group with the joined subquery ,.. – ScaisEdge Aug 19 '18 at 06:41
then it returns duplicates. I need only best matches. – Dumindu Madushanka Aug 19 '18 at 06:45
answer updated.. with distinct .. but you should add a proper data sample and the expected result .. seems that your problem si not simply related to the group by sql_mode .. – ScaisEdge Aug 19 '18 at 06:46
still returns duplicates. okay, let me add sample db structure and data. – Dumindu Madushanka Aug 19 '18 at 06:48
i have added a suggestion for avoid duplicates .. hope is useful – ScaisEdge Aug 19 '18 at 06:52
it seems working fine, When I changed , min(`subject_inv`.`subject_name`) , min(`score`.`custom_score`) to , max(`subject_inv`.`subject_name`) – Dumindu Madushanka Aug 19 '18 at 07:22
Is there a way to optimize this query using query ranking method ? – Dumindu Madushanka Aug 19 '18 at 07:24
This query takes a long time time to execute. Is there any other way to optimize this query. maybe grouped ranking method instead of using case ? – Dumindu Madushanka Aug 19 '18 at 07:32
1

for preformance you must check for index .. you should add (if you have not) proper composite index for the column involved in join ,, but you have several OR clause that reduce drastically the performance ..(expecially check for indexes over student.topic1, 2,,,3 ..anyway this seems another question to me – ScaisEdge Aug 19 '18 at 07:36
I have added sample structure/data – Dumindu Madushanka Aug 19 '18 at 07:56
for better performance you should avoid the .topic1, 2,,,3 . as 3 columns and normalize these data in proper table .. anyway ..for a proper eval of indexes you should post a new question and not extend this one .. do the fact your original question is already solved .. in the new question you should explain your question and add also the tables schema (indexes included) ... not only the columns name and data sample .. – ScaisEdge Aug 19 '18 at 08:29

P.Salmon · Accepted Answer · 2018-08-19T11:05:38.167

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)

Performed very well, Earlier it was 0.1 sec and with this query it is just 0.005 sec — Dumindu Madushanka, Aug 19 '18 at 13:35

MySQL LEFT OUTER JOIN select best match without changing sql_mode

2 Answers2