MySQL inner join problem or other equal solution

Question

Hello,

For a few hours I was figuring out how to make an inner join these tables(tables are simplified for this question):

Table message have these fields:

| id | content |

Table message_relation have these fields:

| id | message_id | sender_id | receiver_id |

Table user have these fields:

| id | name |

What I want to do with this table is to select all messages for a user by receiver_id, but also want to know senders name. I have tried something like:

SELECT *
FROM (
`message_relation`
)
JOIN `message` ON `message`.`id` = `message_relation`.`message_id`
JOIN `user` ON `message_relation`.`receiver_id` = `user`.`id`
WHERE `receiver_id` = '10'

With this query I only get receiver_id name, but cant figure out how to find out sender_id within this query too. Any help would be appreciated!

Answer 1

You can join with the user table more than once, using table aliases:

SELECT m.content,
       us.name as 'sender',
       ur.name as 'receiver'
FROM   message_relation mr
JOIN   message m ON (m.id = mr.message_id)
JOIN   user us ON (us.id = mr.sender_id)
JOIN   user ur ON (ur.id = mr.receiver_id);

Answer 2

Replace '?????' with your receiver_id:

SELECT message.id AS mid, message.content AS mcontent, user.name AS uname
FROM message,message_relation,user
WHERE
message_relation.message_id=message.id
AND message_relation.sender_id=user.id
AND message_relation.receiver_id='?????'

Phew! Not tested, but you get the idea.