compare, MySQL - rextester

Language: Layout:

drop table if exists agenda_items;

create table agenda_items (id int, name text);

insert into agenda_items
values
( 1  , 'Item 1' ),
( 2  , 'Item 2' ),
( 3  , 'Item 3' );

drop table if exists join_agenda_items_meetings;

create table join_agenda_items_meetings ( id int, agenda_item_id int, meeting_id int) ;

insert into join_agenda_items_meetings
values
 (1  , 1, 1 ),         
 (2  , 1, 2),
 (3  , 2, 1),
 (4  , 3, 2),
 (5  , 2, 1),
 (6  , 1,3);
 
 drop table if exists meetings;

create table meetings ( id int, date date, committee_id int) ;
 
 
insert into meetings value

(1, '2017-01-01' ,1),
 (2, '2017-02-02' , 2),
 (3, '2017-03-03' , 2);
 
 DROP TABLE IF EXISTS `committees`;
CREATE TABLE committees
    (`id` int, `acronym` varchar(4))
;

INSERT INTO committees
    (id, acronym)
VALUES
    (1, 'Com1'),
    (2, 'Com2'),
    (3, 'Com3')
;

set @mymeeting = 2;
 
 select j.agenda_item_id, m.id, m.date, mp.date, c.acronym
 from meetings m 
 left join join_agenda_items_meetings j on j.meeting_id = m.id
 left join join_agenda_items_meetings jp on jp.meeting_id = m.id -1 and jp.agenda_item_id = j.agenda_item_id
 left join meetings mp on mp.id = jp.meeting_id
 left join committees c on mp.committee_id = c.id
 where m.id = @mymeeting;
 
 
 SELECT agenda_items.id, 
       meetings.id, 
       meetings.date, 
       sub_one.max_date, 
       sub_two.acronym 
FROM   agenda_items 
       LEFT JOIN (SELECT ai.id                AS ai_id, 
                         me.id                AS me_id, 
                         Max(me.date) AS max_date 
                  FROM   agenda_items AS ai 
                         JOIN join_agenda_items_meetings AS jaim 
                           ON jaim.agenda_item_id = ai.id 
                         JOIN meetings AS me 
                           ON me.id = jaim.meeting_id 
                  WHERE  me.date < '2017-02-02' 
                  GROUP  BY ai_id) sub_one 
              ON sub_one.ai_id = agenda_items.id 
       LEFT JOIN (SELECT agenda_items.id       AS age_id, 
                         meetings.date AS meet_date, 
                         committees.acronym    AS acronym 
                  FROM   agenda_items 
                         JOIN join_agenda_items_meetings 
                           ON join_agenda_items_meetings.agenda_item_id = agenda_items.id 
                         JOIN meetings 
                           ON meetings.id = join_agenda_items_meetings.meeting_id 
                         JOIN committees 
                           ON committees.id = meetings.committee_id 
                  WHERE  meetings.date) sub_two 
              ON sub_two.age_id = agenda_items.id 
                 AND sub_one.max_date = sub_two.meet_date 
       JOIN join_agenda_items_meetings 
         ON agenda_items.id = join_agenda_items_meetings.agenda_item_id 
       JOIN meetings 
         ON meetings.id = join_agenda_items_meetings.meeting_id 
WHERE  meetings.id = 2;


absolute service time: 0,41 sec