meetings, MySQL - rextester

Language: Layout:

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')
;

DROP TABLE IF EXISTS `meetings`;
CREATE TABLE meetings
    (`id` int, `date` datetime, `committee_id` int)
;

INSERT INTO meetings
    (`id`, `date`, `committee_id`)
VALUES
    (1, '2017-01-01 00:00:00', 1),
    (2, '2017-02-02 00:00:00', 2),
    (3, '2017-03-03 00:00:00', 2)
;

DROP TABLE IF EXISTS `agenda_items`;
CREATE TABLE agenda_items
    (`id` int, `name` varchar(6))
;

INSERT INTO agenda_items
    (`id`, `name`)
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
    (`id`, `agenda_item_id`, `meeting_id`)
VALUES
    (1, 1, 1),
    (2, 1, 2),
    (3, 2, 1),
    (4, 3, 2),
    (5, 2, 1),
    (6, 1, 3)
;

set @meeting_id = 2;

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
where m1.id = @meeting_id;

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date,
       max(m2.date) as max_date
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2 
    on  am2.agenda_item_id = am1.agenda_item_id
    and am2.meeting_id <> am1.meeting_id
left join meetings m2 
    on  m2.id = am2.meeting_id
    and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;

select am1.meeting_id,
       am1.agenda_item_id,
       m1.date as meeting_date,
       max(m2.date) as max_date,
       (   select c3.acronym
           from meetings m3
           join join_agenda_items_meetings am3 on am3.meeting_id = m3.id
           join committees c3 on c3.id = m3.committee_id
           where am3.agenda_item_id = am2.agenda_item_id
             and m3.date = max(m2.date)
       ) as acronym
from meetings m1
join join_agenda_items_meetings am1 on am1.meeting_id = m1.id
left join join_agenda_items_meetings am2 
    on  am2.agenda_item_id = am1.agenda_item_id
    and am2.meeting_id <> am1.meeting_id
left join meetings m2 
    on  m2.id = am2.meeting_id
    and m2.date < m1.date
where m1.id = @meeting_id
group by m1.id, am1.agenda_item_id;

select * from `committees`;
select * from `meetings`;
select * from `agenda_items`;
select * from `join_agenda_items_meetings`;


absolute service time: 0,42 sec