title: Friends list and unread messages SQL query giving unexpected result
body: <p>I have all day trying to get a result of a SQL query but does not give me the expected result.</p>
  
  <p>My tables at which I consult are:</p>
  
  <p><strong>tcc_friends</strong></p>
  
  <pre><code>id(PK AUTO_INCREMENT)
  user_from (FK tcc_user (nickname) )
  user_to (FK tcc_user (nickname) )
  </code></pre>
  
  <p><strong>tcc_messages</strong></p>
  
  <pre><code>id (PK AUTO_INCREMENT)
  message
  reg_time
  wasRead
  id_room (FK tcc_friends(id))
  </code></pre>
  
  <p>test records that have currently inserted are:</p>
  
  <p><strong>tcc_friends</strong></p>
  
  <pre><code>id_room    user_from    user_to
  5             hu        example@gmail.com
  6             hu        example222222@hotmail.com
  </code></pre>
  
  <p><strong>tcc_messages</strong></p>
  
  <pre><code>id          message      id_room
  1           a            5
  2           b            5
  3           c            3
  </code></pre>
  
  <p><strong>SQL:</strong></p>
  
  <pre><code>select
      u.*,
      f.id_room,COUNT(m.id) as newMessages
  from
      tcc_friends f,
      tcc_user u,
      tcc_messages m
  where
      u.nickname = 'hu' IN (u.nickname = f.user_from and f.user_to='hu') or
      (u.nickname = f.user_to and f.user_from='hu') AND
      (m.id_room = f.id_room and m.wasRead = 0)
  GROUP BY
      u.nickname;
  </code></pre>
  
  <p><strong>RESULT:</strong></p>
  
  <pre><code>id   nickname                id_room    newMessages
  81   example@gmail.com       5          2
  </code></pre>
  
  <p>I'm trying to get a user's friends and also add unread messages but displays only friends who have a message and I'd like to show all friends whether or not unread</p>
  
  <p>Can anybody help me? Regards and Thank you all</p>