MySQL Field Concatenation

Question

Given the following table structure:

CREATE TABLE foo (
  ID INT NOT NULL AUTO_INCREMENT,
  Category INT NOT NULL,
  Name VARCHAR(50) NOT NULL,
  PRIMARY KEY (ID))

Containing data:

ID Category Name
1      1    Item 1-1
2      2    Item 2-1
3      1    Item 1-2
4      2    Item 2-2

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?

If I use this query:

SELECT CONCAT(A.ID, ',', B.ID) FROM foo A CROSS JOIN foo B WHERE A.Category = 1 AND B.Category = 2

I get the following result:

1,2
1,4
3,2
3,4

And I want:

1,2
1,4
2,3
3,4

Any ideas? Is this possible?

Answer 1

Updated answer based upon additional requirement

SELECT CONCAT(LEAST(A.ID, B.ID), ',', GREATEST(A.ID, B.ID)) 
  FROM foo A 
 CROSS JOIN foo B 
 WHERE A.Category < B.Category

Initial answer

SELECT CONCAT(LEAST(A.ID, B.ID), ',', GREATEST(A.ID, B.ID)) 
  FROM foo A 
 CROSS JOIN foo B 
 WHERE A.Category = 1 
   AND B.Category = 2

Answer 2

Sorry for answering my own question. This is what I was after:

SELECT
  GROUP_CONCAT(Z.ID ORDER BY Z.ID)
FROM
  foo A CROSS JOIN foo B
INNER JOIN
  (SELECT ID FROM foo) Z
ON
  Z.ID = A.ID OR Z.ID = B.ID
WHERE
  A.Category = 1 AND B.Category = 2
GROUP BY
  A.ID, B.ID

This allows me to expand to an arbitrary number of cross joins, eg for combinations of 3 different categories:

SELECT
  GROUP_CONCAT(Z.ID ORDER BY Z.ID)
FROM
  foo A CROSS JOIN foo B CROSS JOIN foo C
INNER JOIN
  (SELECT ID FROM foo) Z
ON
  Z.ID = A.ID OR Z.ID = B.ID OR Z.ID = C.ID
WHERE
  A.Category = 1 AND B.Category = 2 AND C.Category = 3
GROUP BY
  A.ID, B.ID, C.ID

Thanks to all those who answered.