title: MySQL query for counting users unread message
body: <p>I am <strong>sending the notification message to multiple devices</strong>. <strong>User can login from multiple devices.</strong> I have separate table which maintains the device ids of each user. When I send the notification, <strong>I will put an entry on the message_data with userid, message etc.</strong>, I will put an entry on the message_status table for each user device. </p>
  
  <p>So that I can maintain each device <strong>notification sent status</strong> and error. </p>
  
  <pre><code>message_data
  id          INT     
  userid      VARCHAR
  message     VARCHAR
  sent_time   DATETIME
  language    TINYINT
  
  message_status
  id              INT
  data_id         INT
  device_id       VARCHAR
  device_type     TINYINT
  sent_time       DATETIME
  status          TINYINT
  error_code      TINYINT (If there is any error, we will have the error code here)
  </code></pre>
  
  <p><strong>I want to get the unread notification message count for each user.</strong> status field 0 represents , the message for this device is not yet read by user. If it is 1, User have read the message.</p>
  
  <p><strong>A user can login from multiple devices, If user reads the message in at-least one device, It should be considered as notification message is read.</strong> </p>
  
  <p>If the notification message is not read by user in any device, It needs to be considered as unread message.</p>
  
  <p>How to identify the users with unread notification message count ? Any Idea on this ?</p>