title: I want to fetch Group Chat's Unread comment list (mysql, SQL)
body: <p>I have designed a group chat(read function with, user and staff are many to many) of table structure.</p>
  
  <p>There is I want to get a list of unread messages of the staff, but I can not. If you write what a query, or will retrieve a list of unread messages.</p>
  
  <p>SQL</p>
  
  <pre><code>    select * from chat as c inner join chat_group as cg on c.chat_group_id = cg.id left join chat_read cr on c.id = cr.chat_id
  </code></pre>
  
  <p>Table structure</p>
  
  <pre><code>    user: id, name
  
      staff: id, name
  
      chat_group: id, name
  
      chat: id, chat_group_id, user_id, staff_id, comment
  
      chat_read: id, chat_id, user_id, staff_id
  </code></pre>
  
  <p>Data example</p>
  
  <pre><code>    user:
      1, hoge_user
      2, foo_user
  
      staff:
      1, hoge_staff
      2, foo_staff
  
      chat_group:
      1, test_group
  
      chat:
      1, 1, null, 1, "hello hoge_staff"
      2, 1, 1, null, "hello hoge_user"
  
      chat_read: (Comments posted on their own will not be registered as read)
      1, 1, null, 1
      2, 2, 1, null
      3, 1, null, 2 &lt;- foo staff also read not do anything
      4, 2, null, 2
  </code></pre>