title: GROUP_CONCAT or alternative for grouping many to one MySQL results
body: <p>I currently have database setup like so:</p>
  
  <pre><code>CREATE TABLE `article` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` int(11) NOT NULL,
    `body` int(11) NOT NULL,
    `link` int(11) NOT NULL,
    `date` datetime NOT NULL,
    PRIMARY KEY (`id`)
  )
  
  CREATE TABLE `translation_pivot` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `content` text,
    PRIMARY KEY (`id`)
  )
  </code></pre>
  
  <p>This is quite a simplified version to illustrate the question, essentially the translation_pivot is used to further look up text strings from a series of language tables, but's that's not relevant. Here, the <code>title</code>, <code>body</code> and <code>link</code> columns in <code>article</code> contain an id referencing <code>content</code> from <code>translation_pivot</code>.</p>
  
  <p>The difficulty is that doing an INNER JOIN will result in a column called <code>content</code>, which will contain only the first match from <code>translation_pivot</code>, in this case <code>title</code>. </p>
  
  <p>The other option I've looked into is using GROUP_CONCAT on <code>translation_pivot.content</code>. This will work but then I'm left with a comma separated list of items and lose the obvious relation to <code>title</code>, <code>body</code> and <code>link</code> other than as the first, second and third items (which is ok, but not great). The more serious problem is that items in the translation can be several paragraphs of text. The default value for group_concat_max_len is 1024, which I can change but does this have performance implications if set to high values?</p>
  
  <p>Ideally I'd like a way of replacing the <code>title</code>, <code>body</code> and <code>link</code> columns with the textual result from <code>translation_pivot</code>, or at least getting the textual content back for each as a separate column. Is this possible in a single query?</p>
  
  <p>My other alternative is to retrieve key, value pairs as an array from translation_pivot with the id as the key and then do a lookup after querying the articles. This is only an extra query and probably a lot simpler.</p>
  
  <p>Which solution will scale best? Or is there something else I'm missing?</p>