JOIN on conditional field

Question

I have a table of messages:

id
sender_id
recipient_id

I want to pull all the messages of user (id:1) where he was either the sender or the recipient and JOIN the "other guy" he was talking to.

something like:

SELECT * FROM `message`
LEFT JOIN `user` AS u ON IF(sender_id != 1, sender_id = u.id, recipient_id = u.id)
WHERE sender_id=1 OR recipient_id=1

the reason I don't join both the sender and recipient is that I already have the user I'm searching on, so I think it's a waste to join him on every message - unless you tell me otherwise!

This query I got from another SO question, but I've read it's not efficient at all, so what will be a better query?

Answer 1

This would work:

SELECT *
FROM message
JOIN users ON (message.sender_id = 1 AND user.id = message.recipient_id)
           OR (message.recipient_id = 1 AND user.id = message.sender_id)

Note that there are various other ways of doing this in a more performant fashion. This is the most straightforward method that I can see.