CODEWITHSUNDEEP
mysqlgroup-byaggregate-functions
BrianS
BrianS

Reputation: 57

More than one result in a field

If I have a table "messages" and users can send the same message to more than one user, when I show the messages how can I group all the users in a field? 

ID  SUBJECT MESSAGE    FROM     TO
 1  Test 1  Message       1     John    
 2  Test 2  Message       2     Jackson

THIS IS MY SELECT:

SELECT messages.id_message, subject, body_message, name_user, 
from messages, registro_mensajes
INNER JOIN users on messages.id_user_sender = users.id_user

DATABASE:

MESSAGES

id_message
subject
body_message
id_user_sender

REGIST

id_regist
id_message
id_user_sender
id_user_destin

Thx

Upvotes: 1

Views: 88

Answers (1)

O. Jones
O. Jones

Reputation: 108839

GROUP_CONCAT() is the excellent, though MySQL-specific, aggregate function for handling this requirement.

You can try something like this.

 SELECT a.message_id, a.message_text, 
        GROUP_CONCAT(b.user ORDER BY b.user) users
   FROM message a
   LEFT JOIN destination b ON a.message_id = b.message_id
  GROUP BY a.message_id, a.message_text

(Sorry, I cannot figure out your table structure from your question, so this is an example).

The trouble with GROUP_CONCAT(), and the reason some developers avoid it, is this: it converts perfectly good normalized data into a denormalized resultset. Comma-separated values in columns are a real hassle. But if you only use it for display, it should be fine.

Upvotes: 1

Related Questions