tags:

views:

31

answers:

1

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
+3  A: 

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
VoteyDisciple
Hi Votey, thanks for the excellent example and explanation. I am going to try and rewrite this query as such. I will report back with what I find. Thank you again.
Hi Votey. Are you still around? I cannot get this to work.
Well, it looks like you spilt. I'm going to give you the credit for this answer because I am certain that this will do what I need. I will cintinue to work on it. Thanks Votey.
What specifically doesn't work?
VoteyDisciple
Votey, I am going to post the actual DDL above. I am basically still getting 2 rows instead of one.
It was a nice idea but I don't think this is what I need. I need a single row as I stated in my post not multiple rows.
Your `JOIN` clause includes none of the conditions mine does. You cannot simply join on the ID, or you will end up with two completely duplicated rows. From the SQL statement you have it's still not clear to me what columns need to be involved, but you do need to include conditions for ALL the fields you want to match, including the inequality as I described above. The query I wrote will return exactly one row for the conditions you stated.
VoteyDisciple