title: Getting data from three tables in one query
body: <p>I am trying to retrieve data from three tables at once. The tables look like:</p>
  
  <p><em>categories</em></p>
  
  <pre><code>id
  category
  messageid
  </code></pre>
  
  <p><em>messages</em></p>
  
  <pre><code>id
  title
  message
  </code></pre>
  
  <p><em>comments</em></p>
  
  <pre><code>id
  messageid
  message
  </code></pre>
  
  <p>What I am trying to get is 1 message (because I have a <code>WHERE</code> clause based on id <code>3</code>), 3 categories (because there are 3 categories linked to the message) and 2 comments (because there are 2 comments linked to the message).</p>
  
  <p>I'm trying to retrieve the data by usnig the following query:</p>
  
  <pre><code>SELECT categories.category, messages.id, messages.title, messages.message, comments.count, comments.id as commentid, comments.message as commentmessage
  FROM categories
  RIGHT JOIN 
    (SELECT id, title, message
    FROM messages WHERE messaged.id = 3) messages
  ON messages.id = categories.messageid
  LEFT JOIN
    (SELECT count(id), id, message
    FROM comments
    GROUP BY id, message) comments
  ON messages.id = comments.messageid
  ORDER BY article.id DESC
  </code></pre>
  
  <p>However when running this query I get 6 results:</p>
  
  <pre><code>category      id  title      message      count  commentid  commentmessage
  test          3   the title  the message  1      6          comment 1
  test          3   the title  the message  1      5          comment 2
  installation  3   the title  the message  1      6          comment 1
  installation  3   the title  the message  1      5          comment 2
  question      3   the title  the message  1      6          comment 1
  question      3   the title  the message  1      5          comment 2
  </code></pre>
  
  <p>Where I expected a result something like:</p>
  
  <pre><code>category      id  title      message      count  commentid  commentmessage
  test          3   the title  the message  1      6          comment 1
  question      3   the title  the message  1      5          comment 2
  installation  3   the title  the message  1      null       null
  </code></pre>
  
  <p>With only three rows I should be able to get all the needed data. Is this even possible? Am I doing it completely wrong ™?</p>