modified, 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), 
    (4, '2017-02-02 00:00:00', 3)
;

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'), 
    (4, 'Item 4')
;

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, 4),
    (6, 1, 3), 
    (7, 2, 2), 
    (8, 4, 4),
    (9, 4, 2), 
    (10, 4, 3), 
    (11, 3, 3)
;

-- ORIGINAL QUERY BUT WITH agenda.name ADDED.

SELECT agenda_items.id,
       agenda_items.name,
       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;

-- QUERY FROM Stefano Zanini.

select    me.ID, me.DATE, rel.AGENDA_ITEM_ID, sub.MAX_DATE, co.ACRONYM
from      MEETINGS me
join      JOIN_AGENDA_ITEMS_MEETINGS rel /* Note 1*/
  on      me.ID = rel.MEETING_ID
left join (   
              select  t1.AGENDA_ITEM_ID, max(t2.DATE) MAX_DATE
              from    JOIN_AGENDA_ITEMS_MEETINGS t1
              join    MEETINGS t2
                on    t2.ID = t1.MEETING_ID
              where   t2.DATE < '2017-02-02'
              group by t1.AGENDA_ITEM_ID
          ) sub
  on      rel.AGENDA_ITEM_ID = sub.AGENDA_ITEM_ID /* Note 2 */
left join JOIN_AGENDA_ITEMS_MEETINGS rel2
  on      rel2.AGENDA_ITEM_ID = rel.AGENDA_ITEM_ID /* Note 3 */
left join MEETINGS me2
  on      rel2.MEETING_ID = me2.ID and
          sub.MAX_DATE = me2.DATE /* Note 4 */
left join COMMITTEES co
  on      co.ID = me2.COMMITTEE_ID
where     me.ID = 2 and
          (sub.MAX_DATE is null or me2.DATE is not null) /* Note 5 */
order by  rel.AGENDA_ITEM_ID, rel2.MEETING_ID;

-- QUERY 1 FROM Paul Spiegel.

set @meeting_id = 2;

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;

-- QUERY 2 FROM Paul Spiegel.

select t.*, c.acronym
from (
    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
) t
left join join_agenda_items_meetings am on am.agenda_item_id = t.agenda_item_id
left join meetings m
    on  m.id   = am.meeting_id
    and m.date = t.max_date
left join committees c on c.id = m.committee_id
where t.max_date is null or c.acronym is not null;