MySQL select values based on a sub query
select 
  t2.app_status,
  count(*) as status_count
from 
  tbl_appointment t2 
INNER JOIN 
  tbl_notification_flow t1 ON t1.notification_type = t2.app_status
WHERE t1.notification_type != 'Other'

group by t2.app_status ;


with result:

Booked      13
Defaulted   1
Missed      4
Notified    1