SO46343796 ver3, MySQL

Language: Layout:

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

Drop table if exists SO46343796_Follow_Up;
drop table if exists SO46343796_Logs;
drop table if exists SO46343796_allCode;

Create table SO46343796_Follow_Up  (
    follow_up_Id int,
    Feedback_Close varchar(100),
    feedback_open varchar(100),
    is_email_required tinyint,
    is_open tinyint,
    Reminder_date date,
    Client_ID int,
    conclusion_id int,
     stage_id int);

create table SO46343796_Logs  (
 user_id int,
 menu_id int,
 logs_time datetime,
 tran_id int,
 `transaction` varchar(20));
 
Create table SO46343796_allCode  (
 code_type varchar(50),
 code_id int,
 srno int,
 code_name varchar(50));

Insert into SO46343796_Follow_Up values (1, NULL, 'dsffsdfsdfsd',                                     1, 1,'2017-09-20', 101, 96,  72);
Insert into SO46343796_Follow_Up values (2, NULL, 'FSGDFHFGHFG',                                      1, 1, '2017-09-28', 101, 251, 72);
Insert into SO46343796_Follow_Up values (3, NULL, 'Tender stage fb',                                  0, 1, NULL        , 101, 98,  163);
Insert into SO46343796_Follow_Up values (4, NULL, 'Call back tender stage update date from 28 to 30', 1, 1, '2017-09-28', 101, 96,  163);
Insert into SO46343796_Follow_Up values (5, NULL, 'Metting follow up for next meeting',               1, 1, '2017-10-02', 101, 96,  73);

Insert into SO46343796_Logs values (84,      69, '2017-09-19 19:31:04',       1 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-19 19:31:25',       2 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-20 19:10:41',       2 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-21 12:35:01',       3 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-21 12:35:26',       4 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-21 12:36:16',       4 , 'FLWUP');
Insert into SO46343796_Logs values (84,      69, '2017-09-21 12:38:30',       5 , 'FLWUP');

Insert into SO46343796_AllCode values ('MARKETING_STAGES' ,      72 ,    1 , 'Enquiry');
Insert into SO46343796_AllCode values ('MARKETING_STAGES' ,      73 ,    3 , 'Meeting');     
Insert into SO46343796_AllCode values ('MARKETING_STAGES' ,      74 ,    4 , 'Presentation');
Insert into SO46343796_AllCode values ('MARKETING_STAGES' ,     163 ,    2 , 'Tender');
Insert into SO46343796_AllCode values ('96' ,      96 ,    2 , 'Call Back');
Insert into SO46343796_AllCode values ('251' ,     251 ,    2 , 'ISO');
Insert into SO46343796_AllCode values ('98' ,      98 ,    2 , '98');

#xQbert's answer
SELECT f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
FROM  SO46343796_follow_up f 
INNER JOIN SO46343796_logs l on l.tran_id = f.follow_up_id 
INNER JOIN SO46343796_allcode ast on ast.code_id = f.stage_id 
INNER JOIN SO46343796_allcode ac on ac.code_id = f.conclusion_id 
JOIN (SELECT max(follow_up_ID) MFID, stage_ID 
      FROM SO46343796_follow_up 
      GROUP BY stage_ID) Z 
  on f.follow_up_ID = Z.MFID 
 and F.Stage_ID = Z.Stage_ID
WHERE l.transaction='FLWUP' and f.client_id = 101 
GROUP BY f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, ast.code_name , ac.code_name
ORDER BY ast.srno;

#original query from question with full group by to show fields being consolidated that shouldn't be since elments were missing in group by.
select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from SO46343796_follow_up f 
join SO46343796_logs l on l.tran_id = f.follow_up_id 
join SO46343796_allcode ast on ast.code_id = f.stage_id 
join SO46343796_allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, ast.code_name , ac.code_name
order by ast.srno;

#Rupal's response showing 1,4,5 not the 2,4,5 expected.
select f.follow_up_id,f.feedback_open, f.feedback_close, f.reminder_date, 
ast.code_name as stage, ac.code_name as conclusion, max(l.logs_time)  
from SO46343796_follow_up f 
join SO46343796_logs l on l.tran_id = f.follow_up_id 
join SO46343796_allcode ast on ast.code_id = f.stage_id 
join SO46343796_allcode ac on ac.code_id = f.conclusion_id 
where l.transaction='FLWUP' and f.client_id = 101 
group by f.stage_id order by ast.srno, f.follow_up_id DESC;

Drop table if exists SO46343796_Follow_Up;
drop table if exists SO46343796_Logs;
drop table if exists SO46343796_allCode;


absolute service time: 0,44 sec