tags:

views:

12

answers:

1

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

Your last join (t3.employee_id = t4.employee_id) is clearly wrong - you're joining employee on itself. You need to replace that condition with join on either mailbox or mail_message - it's hard to say without seeing your exact table structure. Can you post it?

ChssPly76
Hi Chss. I don't have the structure here, it's on a server at work and I don't have access after 6:00pm. The VPN is down. All I have is the SQL. I figured that the SQL was not correct. I think I need to probably join on mailbox. Do you have any other suggestions>?
I'd guess you need to join on `mail_message` - presumably it has some sort of `sender_id` and `recipient_id`. Mind you, I'm guessing based solely on table name. You do need to join `employee` twice, once as sender and once as recipient - take a look at your tables and you should be able to figure out what columns to join. If not, post the structure (and / or sample data) here when you get a chance and I'll take a look.
ChssPly76