title: SQL query that joins two tables and returns multiple matches from one column?
body: <p>My title is terrible, and that's probably why I'm not finding what I want on Google.</p>
  
  <p>What I'm trying to do is export some data from an old in-house blog so I can import it into something else.  My issue is that while I can kind of create the sort of JOIN I'm looking for, the match in the second table can contain multiple rows, so I end up with tons of duplicate data.  I need to take the results from the second table and concat those (if there are multiple matches) into a single field in the query result.  There is no need for a WHERE constraint on the query, I'm trying to retrieve the entire blog_posts table.</p>
  
  <p>Hopefully this abbreviated layout of the table structure will help illustrate:</p>
  
  <pre><code>blog_posts              blog_categories
  ---------------------------------------
  post_id                 post_id
  post_content            category_id
  post_author
  </code></pre>
  
  <p>And here's some sample data.</p>
  
  <p>blog_posts table data:</p>
  
  <pre><code>post_id  post_content  post_author
  ----------------------------------
  1        foo1          bob
  2        foo2          bob
  3        foo3          fred
  </code></pre>
  
  <p>blog_categories table data:</p>
  
  <pre><code>post_id  category_id
  --------------------
  1        1
  1        2
  1        6
  2        1
  3        2
  3        4
  </code></pre>
  
  <p>And what my ideal results would look like would be this:</p>
  
  <pre><code>post_id  post_content  post_author  category_ids
  ------------------------------------------------
  1        foo1          bob          1,2,6
  2        foo2          bob          1
  3        foo3          fred         2,4
  </code></pre>
  
  <p>The closest I could get was a simple join like this:</p>
  
  <pre><code>SELECT 
      blog_posts.post_id, 
      blog_posts.post_content, 
      blog_posts.post_author, 
      blog_categories.category_id 
  FROM blog_posts 
      INNER JOIN blog_categories 
          ON blog_posts.post_id = blog_categories.post_id
  </code></pre>
  
  <p>But that returns matches in the blog_posts table multiple times (one time for each category_id that matches).</p>
  
  <p>Is there any way to accomplish what I want using just SQL?  I'm thinking some sort of sub-select would work, but what I can't wrap my head around how that would work - I know I'd essentially want to do a select in my "loop" for the category ids using the current post id, but the syntax for that escapes me.  It need not be efficient, this is a one-time operation.</p>