Memebers with Reservations - Query for Push Notification

select

*
from

(select
distinct m.id as mid,
m.email
from members m
where m.state = 'normal'
and m.imaginary = 'normal'
and m.email not like '%%@socar.my'
and CONVERT_TZ(m.created_at, '+00:00', '+8:00') >= '2019-1-1 00:00') A

left join

(select
r.id as rid,
r.member_id as mid,
Date(CONVERT_TZ(r.start_at, '+00:00', '+8:00')) as date
from reservations r
where CONVERT_TZ(r.start_at, '+00:00', '+8:00') >= '2019-1-1 00:00'
and r.state = 'completed') B

on A.mid = B.mid



order by B.date;

########################Updated Query

select
m.id as mid,
m.email as email,
m.display_name as displayname,
m.first_name as firstname,
date(m.created_at + interval '8' hour) as date,
IFNULL((select distinct r.member_id from reservations r where r.state = 'completed' and r.member_id = m.id),0) as matchedmid
from members m
Where m.imaginary = 'normal'
and m.state = 'normal'
and m.email not like '%%@socar.my';