title: Inner Join with subquery and or
body: <p>I am building a chat with several conversations and have a task to extract latest messages from each conversation.
  By conversation I mean messages from or to particular user. (It is defned by <code>fromid</code> or <code>toid</code> column).</p>
  
  <p>My plan:</p>
  
  <p>1) Extract id's of conversations</p>
  
  <p>2) Extract messages themselves </p>
  
  <p>Based on anwers to similar questions here, I build this query that failed to execute ("You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE <code>fromid</code> != 4 LIMIT 10) table2 ON (table2.VALIDID = `support_chat_message' at line 1")</p>
  
  <p>My query is </p>
  
  <pre><code>SELECT `fromid`, `toid`, `message`, `sendat`, `messageid` 
  FROM `support_chat_messages` INNER JOIN
               (SELECT  `fromid` as VALIDID 
               FROM  `support_chat_messages`  
               GROUP BY  `fromid`
               WHERE `fromid` != 4 LIMIT 10) table2 
          ON  (table2.VALIDID = `support_chat_messages`.`fromid`) 
          OR (table2.VALIDID = `support_chat_messages`.`toid`) ORDER BY `messageid`
  </code></pre>
  
  <p>Some explanations:</p>
  
  <p>1) I select <code>fromid</code> and group them via same field to get conversations (=users ids). Users id are named VALIDID </p>
  
  <p>2) Where <code>fromid</code> != 4 mean that not messages from me (only other users id).</p>
  
  <p>3) Limit 10 - no more than 10 conversations.</p>
  
  <p>4) I search the table support_chat_messages for message that was send from selected user (<code>table2.VALIDID = support_chat_messages.fromid</code>) or to selected user (<code>table2.VALIDID = support_chat_messages.toid</code>)</p>
  
  <p>What is the problem with query?</p>
  
  <p>How to limit query? I need to get the latest message from each conversation. IF I put limit 1 at the end I will get just latest conversation, but not 1 from each.</p>
  
  <p>Do you know how to optimize it? I will refresh every 2 sec and I such query will seriously load the system. I have an idea to use groupby (fromid or toid) without join, but not sure that such expression is valid. </p>
  
  <p>Thanks for help.</p>
  
  <p>Here's a fiddle of same.  sqlfiddle.com/#!2/796c4/4/0</p>
  
  <p>Desired result would like this...</p>
  
  <p>Conversations are (7 and 4), (6 and 4), (5 and 4), (2 and 4) For each top messages are (22), (21), (20), (18) </p>