title: How to to join a table onto itself, grouping the field being joined on?
body: <p>I have a table that creates a record each time a post is created and another table that creates a record each time a post is favorited.</p>
  
  <pre><code>CREATE TABLE `posts` (
    `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
    `title` varchar(100) NOT NULL,
    PRIMARY KEY (`post_id`)
  )
  
  CREATE TABLE `favorited_posts` (
    `user_id` bigint(20) NOT NULL,
    `post_id` bigint(20) NOT NULL,
    KEY `user_id` (`user_id`),
    KEY `post_id` (`post_id`),
    FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`)
  )
  </code></pre>
  
  <p>The following query returns a result set that shows how many times a post has been favorited</p>
  
  <pre><code>SELECT p.post_id, p.title,
      COUNT(fp.post_id) as num_favorites
  FROM posts p
  LEFT JOIN favorited_posts fp
  ON fp.post_id = p.post_id
  WHERE p.post_id BETWEEN 0 AND 10000
  GROUP BY 1;
  
  +-------------------+----------------+
  | post_id  | title  |  num_favorites |
  +-------------------+----------------+
  | 1        | abc    |  15            |
  | 2        | hello  |  0             |
  | 3        | test   |  7             |
  +----------+--------+----------------+
  </code></pre>
  
  <p>How can I change the query to create an ad-hoc column with a true/false, 0/1 value that represents whether a user has favorited a particular post?  It would produce a result set like this (The user has favorited post 2 and 3)</p>
  
  <pre><code>+-------------------+----------------+---------------+
  | post_id  | title  |  num_favorites | has_favorited |
  +-------------------+----------------+---------------+
  | 1        | abc    |  15            | 0             |
  | 2        | hello  |  0             | 1             |
  | 3        | test   |  7             | 1             |
  +----------+--------+----------------+---------------+
  </code></pre>
  
  <p>I can retrieve the posts a particular user has favorited with the query but do not know how to retrieve this information as part of a single query that captures the post_id, title and total number of favorites.</p>
  
  <pre><code>SELECT * FROM favorited_posts
  WHERE user_id = 10;
  </code></pre>