MySQL select values based on a sub query

MySQL

I have two tables , Table #1 => tbl_notification_flow has the following information : 

app_status
Booked
Notified
Missed
Defaulted

With the app_status being the column name and Table #2 => tbl_appointment has the following information : 

id  app_status
1   Booked
3   Missed
4   Missed
5   Missed
6   Missed
7   Booked
8   Booked
9   Booked
10  Booked
11  Booked
12  Booked
13  Booked
14  Booked
15  Booked
17  Booked
18  Booked
19  Booked
20  Defaulted
21  Notified

With id and app_status being the columns ,

I want to get a count of all the app_status based on the number of times they have appeared on Table #2 ,

I tried the following query : 

SELECT COUNT(tbl_appointment.id)AS total_count, tbl_appointment.`app_status`
  FROM tbl_appointment
 WHERE (tbl_appointment.`app_status`) IN
 (
   SELECT tbl_notification_flow.`notification_type` AS app_status 
     FROM tbl_notification_flow 
    WHERE tbl_notification_flow.notification_type != 'Other'
 )

It gets the following result : 

total_count app_status
19  Booked

Please help with the best query to get the right results.

Best Answer

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
Related Question