CREATE DEFINER = `root` @`%` PROCEDURE `spGetChatGroupList` ( IN `P_RoleTypeId` INTEGER , IN `P_InstituteId` INTEGER ) BEGIN IF EXISTS ( SELECT ? FROM `tinstitute` WHERE `tinstitute` . `Id` = `P_InstituteId` ) THEN SELECT `tchatgroupmaster` . `Id` AS `ChatGroupId` , `tchatgroupmaster` . `Title` AS `ChatGroupName` , `IFNULL` ( `tchat` . `CreatedDateTime` , ? ) AS `LastMessageTime` , IF ( `ChatDetail` IS NULL , IF ( `STRCMP` ( `SUBSTRING_INDEX` ( `ContentPath` , ?, ... ) , ? OR ? OR ? ) , ? , IF ( `STRCMP` ( `SUBSTRING_INDEX` ( `ContentPath` , ?, ... ) , ? ) , ? , IF ( `STRCMP` ( `SUBSTRING_INDEX` ( `ContentPath` , ?, ... ) , ? ) , ?, ... ) ) ) , `ChatDetail` ) AS `MessageType` FROM `tchatgroupmaster` LEFT JOIN `tchat` ON `tchat` . `ChatGroupId` = `tchatgroupmaster` . `Id` LEFT JOIN `tbatch` ON `tchatgroupmaster` . `BatchId` = `tbatch` . `Id` LEFT JOIN `tteacher` ON `tteacher` . `Id` = `tbatch` . `TeacherId` WHERE `tteacher` . `InstituteId` = `P_InstituteId` AND `tchatgroupmaster` . `CreatedDateTime` <= NOW ( ) AND ( `tchat` . `CreatedDateTime` = ( SELECT MAX ( `tchat` . `CreatedDateTime` ) 