How to select multiple results in one row

Question

The problem: One of our sites has a messaging system between users, for this we have two tables: one that stores the messages between users and the other with users, follow the structures below,and with this data we need to display in the messages the name of all users to which the message was sent.

At first I thought I’d make a select for the messages and with the user id of this message generate another string of select’s within a while, but it seemed wrong to me and searching through the internet I found no alternative to this situation. Would anyone have a solution to this problem? It would have to do a single select to get both users' names and the message?

Follow the structures:

TABLE messages:

• id INT PRIMARY KEY,
• id_users: 'Relates to user id, Ex: 1,27,1247,88',
• title 'message title',
• text 'from the message',

TABLE users:

• id INT PRIMARY KEY,
• name 'user name',
• etc..

Answer 1

Even if you create an SQL for this, it will cost you a lot of processing, because you will have to break this string id_users in a list, and do a JOIN on top of it.

What I suggest is that you refocus your structure, using the way the vast majority of systems work in this situation. This way:

TABLE messages:

id INT PRIMARY KEY,
title 'titulo da mensagem',
text 'texto da mensagem',

TABLE users:

id INT PRIMARY KEY,
name 'nome do usuario',

TABLE users_messages:

id INT PRIMARY KEY
id_user (FK)
id_mensagem (FK)

It is a super basic structure Many-to-Many, ie, a message can belong to 1 or more users, and a user can have 1 or more messages. With that, your query stays this way:

SELECT u.id, u.name, m.id
    FROM users u
    JOIN users_mensagens um ON um.id_users = u.`id`
    JOIN mensagens m ON m.id = um.id_mensagens;

Answer 2

A friend (@rodrigoborth) helped me talking about 'FIND_IN_SET', which made me arrive in the following query:

SELECT mensagens.id, users.name, mensagens.title, mensagens.date
    FROM mensagens INNER JOIN users
        WHERE FIND_IN_SET( users.id , mensagens.id_users ) ORDER BY mensagens.id

Simplifying my problem and thus temporarily answering the question! =)

Answer 3

Its database seems denormalized, I advise if possible, the creation of a table to relate the user to message. But it follows an option using a sub-query to bring users related to the message, reducing the number of query result lines.

SELECT
m.title,
m.text,
m.id_users,
(
  SELECT 
  GROUP_CONCAT(u.name SEPARATOR ';')
  FROM users u 
  WHERE FIND_IN_SET(u.id,m.id_users)
) as nomes
FROM mensagens m

In this case the query returns the users related to the message in the format usuario1;usuário2

Example: Sqlfiddle