title: MySQL Field Concatenation
body: <p>Given the following table structure:</p>
  
  <pre><code>CREATE TABLE foo (
    ID INT NOT NULL AUTO_INCREMENT,
    Category INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    PRIMARY KEY (ID))
  </code></pre>
  
  <p>Containing data:</p>
  
  <pre><code>ID Category Name
  1      1    Item 1-1
  2      2    Item 2-1
  3      1    Item 1-2
  4      2    Item 2-2
  </code></pre>
  
  <p>How do I construct a query to return an every possible combination of rows by category, containing a concatenated list of the ID fields in order?</p>
  
  <p>If I use this query:</p>
  
  <pre><code>SELECT CONCAT(A.ID, ',', B.ID) FROM foo A CROSS JOIN foo B WHERE A.Category = 1 AND B.Category = 2
  </code></pre>
  
  <p>I get the following result:</p>
  
  <pre><code>1,2
  1,4
  3,2
  3,4
  </code></pre>
  
  <p>And I want:</p>
  
  <pre><code>1,2
  1,4
  2,3
  3,4
  </code></pre>
  
  <p>Any ideas? Is this possible?</p>