CODEWITHSUNDEEP
phpmysql
0plus1
0plus1

Reputation: 4555

Messaging system. Multiple recipients, retrieving data. Best Practice

I have two tables:

Messages:

id
message_text
..omitted..

Recipients

id
message_id
recipient_id
..omitted..

I would like to show in a table the message you sent, and whom you sent to.

to                        message        date

paul,roger,micheal        howdy          2011 12 12
paul                      hello          2011 12 12
paul,micheal              hi             2011 12 12

Obviously since there are multiple recipients, a join query will return x duplicate records for x number of recipients, grouping for id will return only the first recipient.

What is the best practice this case? Should I make two separate queries (one for the message itself, one for the recipients)? Are there alternatives?

Thank you very much

Upvotes: 3

Views: 415

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270677

Try this with GROUP_CONCAT()

SELECT
  GROUP_CONCAT(recipient_name) AS recipients,
  message_text AS message,
  date
FROM Messages JOIN Recipients ON Messages.id = Recipients.message_id
GROUP BY Recipients.message_id, message_text, date

Upvotes: 2

Maxim Krizhanovsky
Maxim Krizhanovsky

Reputation: 26719

In MySQL, you can accomplish this with a GROUP BY and GROUP_CONCAT (so all receipients will be concatanated in one field in the resultset)

Upvotes: 1

Related Questions