CODEWITHSUNDEEP
mysql
Lawrence Cooke
Lawrence Cooke

Reputation: 1597

Getting Group by with left join to work in MySQL 5.7

Im trying to get a query to only return a single row for each unique ID.

given the following:

CREATE TABLE `groups` (   `id` int(11) NOT NULL AUTO_INCREMENT,  
`name` varchar(45) DEFAULT NULL,   `type` int(1) DEFAULT NULL,  
`date` datetime DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB
AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

CREATE TABLE `groupmembers` (   `id` int(11) NOT NULL AUTO_INCREMENT, 
`username` varchar(255) DEFAULT NULL,   `readmessage` int(1) DEFAULT
NULL,   `status` int(1) DEFAULT NULL,   `group_id` int(20) DEFAULT
NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT
CHARSET=latin1;

CREATE TABLE `messages` (   `id` bigint(10) NOT NULL AUTO_INCREMENT,  
`send_to` varchar(50) NOT NULL DEFAULT '',   `name` varchar(50) NOT
NULL DEFAULT '',   `email` varchar(50) NOT NULL DEFAULT '',  
`subject` varchar(100) NOT NULL DEFAULT 'No Subject',   `message`
longtext NOT NULL,   `timestamp` timestamp NOT NULL DEFAULT
CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   `message_read`
enum('Yes','No') NOT NULL DEFAULT 'No',   `send_time` datetime NOT
NULL DEFAULT '1970-01-01 00:00:00',
  `hide_message` enum('0','1') NOT NULL,   `grouper` int(20) DEFAULT
NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT
CHARSET=latin1;

insert into groups (type,date) values ('0',now()); 
insert into groupmembers (username,group_id) values ('user1',5),('user2',5);
insert into messages (send_to,name,send_time,grouper,message) values
('user1','user2','2019-10-09 19:18:12',5,'hello'); 
insert into messages (send_to,name,send_time,grouper,message) values
('','user2','2019-10-10 09:18:39',5,'hello');

In mysql 5.6 this query works as anticipated:

select groups.id,send_to,messages.name,max(send_time) from groups
inner join messages on groups.id=messages.grouper left join
groupmembers on groups.id=groupmembers.group_id where
groupmembers.username='user1' group by groups.id  ORDER BY
MAX(send_time)

in mysql 5.7 this returns an error

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY
clause and contains nonaggregated column 'messages.send_to' which is
not functionally dependent on columns in GROUP BY clause; this is
incompatible with sql_mode=only_full_group_by

What do I need to do to get this to work in MySQL 5.7 without adjusting the SQL Mode?

See SQL Fiddle here: http://sqlfiddle.com/#!9/9061da/4

It works as expected on the SQL fiddle as that is running 5.6.

Upvotes: 0

Views: 38

Answers (1)

Strawberry
Strawberry

Reputation: 33935

Just a guess...

SELECT DISTINCT g.id
              , m.send_to
              , m.name
              , m.send_time 
           FROM groups g
           JOIN messages m
             ON m.grouper = g.id 
           JOIN
              ( SELECT send_to
                     , name
                     , MAX(send_time) send_time
                  FROM messages m
                 GROUP
                    BY send_to
                     , name
              ) x
             ON x.send_to = m.send_to
            AND x.name = m.name
            AND x.send_time = m.send_time
           JOIN groupmembers gm
             ON gm.group_id = g.id 
          WHERE gm.username = 'user1' 
          ORDER 
             BY send_time;

Upvotes: 1

Related Questions