title: SQL Server messages system with conversation list
body: <p>Good morning!</p>
  
  <p>I have developed a web application to allow people to exchange messages.</p>
  
  <p>I use SQL Server 2008 and Classic ASP on Windows 2012 Server.</p>
  
  <p>To simplify, suppose there are only 2 tables:</p>
  
  <pre><code>tUsers
    Id INT IDENTITY(1,1)
    Username VARCHAR(20)
  
  tMessages
    Id INT IDENTITY(1,1)
    UserIdFrom INT
    UserIdTo INT
    SentDate DATETIME
    Message VARCHAR(300)
    Read TINYINT
  </code></pre>
  
  <p>My question is simple, but I can't find a solution specific for MS SQL.</p>
  
  <p>I need to write a query that returns the conversation list between me and other users I talked with.</p>
  
  <p>The list must contain <em>UserId</em> and <em>Username</em> (not mine but of the users I talked with) and must be in reverse order by sent date/time order, and must include the message (from or to me) without answer (as Whatsapp for example).</p>
  
  <p>I found many solutions, but for MySQL the conversion to MS SQL is not working. This is the first time I need help on SQL, but I can't find a solution by myself.</p>
  
  <p>Can anyone give me an example of T-SQL query to solve my problem???</p>
  
  <p>Sorry for my English, I used to speak Italian.</p>
  
  <p>This is my second question on StackOverflow. I hope I have respected the rules.</p>
  
  <p>Thanks in advance.</p>