I asked this question before but still have not been able to get this working. Someone suggested a self join but I don't see where that will work. I am still getting 2 rows with the incorrect results. The self join is still present but I just can't seem to get the results I need.
I want to go from this:
id sender recipient
1 Administrator Administrator
1 Miller Miller
To this:
id sender recipient
1 Administrator Miller
Here is the SQL that I am working with.
SELECT
t1.msg_id AS id,
t3.lastname AS sender,
t4.lastname AS recipient
FROM
mail_message AS t1
Inner Join mailbox AS t2 ON t2.msg_id = t1.msg_id
Inner Join employee AS t3 ON t3.employee_id = t2.employee_id
Inner Join employee AS t4 ON t3.employee_id = t4.employee_id