Need some support writing a MySQL-Query

Question

I'm writing a basic message-script in PHP/MySQL but I'm stuck at a database query right now. I'll appreciate any hints or assistance (:

I'm using two tables, since a message can be sent to several users:

messages:
id | sender_id | subject | ...

message_receivers:
message_id | receiver_id | ...

What I want to do now is display a message to the user that he selects. But I want to show the whole message history the user had in that conversation (jumping in browser to the one he selected). Doing this with a join is quite simple:

SELECT * FROM messages
    JOIN message_receivers 
    ON messages.id = message_receivers.message_id
    WHERE sender_id = x
    AND receiver_id = y

But now I'm missing the information of other receivers of a message! And I have no clue how to get this information. Any ideas for that? (:

Answer 1

Join the message_receivers table one more time to retrieve the other recipients of the message:

SELECT
    m.id, m.sender_id, m.subject,
    r.receiver_id AS recipient,
    c.receiver_id AS carboncopy
FROM messages AS m
INNER JOIN message_receivers AS r
    ON m.id = r.message_id
LEFT OUTER JOIN message_receivers AS c
    ON r.message_id = c.message_id AND r.receiver_id != c.receiver_id
WHERE m.sender_id = x AND r.receiver_id = y

The recipient that your are interested in will be in column recipient (in every result record). Other recipients will be in column carboncopy (one per result record). If carboncopy is NULL, then the message had only a single receiver.