create table Membership

(

    id   int primary key,

    name varchar(50)

);

create table MembershipType

(

    id   int primary key,

    name varchar(50) unique not null

);

create table MembershipEvent

(

    id               int primary key,

    membershipId     int      not null,

    membershipTypeId int      not null,

    eventTime        datetime not null,

    foreign key (membershipTypeId) references MembershipType (id),

    foreign key (membershipId) references Membership (id)

);

select M.id as MembershipId, M.name as memberName, MT.name as currentMembershipTypeName, ME.eventTime as currentMembershipStartTime

from Membership M

         left join MembershipEvent ME on M.id = ME.membershipId

         left join MembershipType MT on ME.membershipTypeId = MT.id

where exists(select *

             from (select InnerME.id, InnerME.membershipTypeId

                   from MembershipEvent InnerME

                   where InnerME.membershipId = ME.membershipId

                     and InnerME.eventTime <= NOW()

                   order by InnerME.eventTime desc

                   limit 1) TEMP

             where TEMP.membershipTypeId not in (4)

               and TEMP.id = ME.id);