Can I take the results from two rows and combine them into one?

Question

I have 2 rows of data in a mysql database. Both rows are identical with the exception of one column. What I am looking for is a query that will produce a single row with both distinct values. Can someone please help me out?

An example of the data is:

1    Administrator    Test    2009-08-17 03:57:35
1    Miller           Test    2009-08-17 03:57:35

What I need is this:

1    Administrator    Miller    Test    2009-08-17 03:57:35

DISTINCT won't give it to me. I need the additional column added or appended to the result set.

---

This is the code I am trying to use.

SELECT
t2.msg_id,
t3.lname AS sender,
t4.lname AS recipient
FROM
mail_message AS t1
Inner Join mailbox AS t2 ON t1.msg_seq = t2.msg_seq
Inner Join employee AS t3 ON t3.employee_id = t2.employee_id
INNER JOIN employee AS t4 ON t3.employee_id = t4.employee_id

Answer 1

Sure. You can JOIN the table onto a copy of itself, with a technique something like this:

SELECT t1.*, t2.name
FROM the_table AS t1
INNER JOIN the_table AS t2 ON (t1.id = t2.id AND t1.test = t2.test AND t1.date_column = t2.date_column AND t1.name < t2.name)

Here we arbitrarily name one copy of the table t1 and the other t2 and join rows that are identical except for the name.

Note that I do not simply do a != test on the name, since that would result in two matching rows: one where Administrator is in t1 and the other where Administrator is in t2. To distinguish one identical table from the other, I assert that t1 will always be the one with a "smaller" (i.e., earlier alphabetically) name.

If there can be three or more identical rows, this will still work, but will return several matches. For example, if A, B, and C are all names in otherwise identical rows, you'd get:

A B
B C
A C