MySQL multiple keys in a single row

Question

I have 3 tables; Inbox, Outbox, and Messages.

For a single message, the data looks like this.

Message:

+----------+----------------+
|message_id|content         |
+----------+----------------+
|1         |'lorem ipsum...'|

Inbox:

+--------+----------+-----+-------+
|inbox_id|message_id|to_id|from_id|
+--------+----------+-----+-------+
|1       |1         |1    |1      |
|2       |1         |2    |1      |
|3       |1         |3    |1      |

Outbox:

+---------+----------+-----+-------+
|outbox_id|message_id|to_id|from_id|
+---------+----------+-----+-------+
|1        |1         |1,2,3|1      |

A single user can send PMs to multiple people. For each recipient, a new inbox record is generated.

As of right now, Im generating one outbox record regardless of recipients.

Upon sending a PM i am able to generate exactly the data shown above but I am having issues trying to generate usernames for the outbox.

I'd like to be able to do something like this for the outbox (the below query is what I'd like to accomplish but of course doesn't work).

SELECT
  users.username,
  messages.message_id,
  messages.subject
FROM
  messages
JOIN
  outbox ON outbox.message_id = message.message_id
JOIN
  users ON users.user_id IN (outbox.to_id)
WHERE
  outbox.from_id = 1

The end result being

+-------------------+----------+----------------+
|username           |message_id|subject         |
+-------------------+----------+----------------+
|user1, user2, user3|1         |'lorem ipsum...'|

score 4 · Accepted Answer · edited May 23 '17 at 12:04

4

MySQL's IN doesn't work the way you think it does. outbox.to_id is a string, and users.user_id is an int. MySQL is trying to convert to_id to an int, and is stopping when it gets to the ',' (the first non-int character), therefore '1,2,3' converts to '1'.

This question that I asked will help you, read the accepted answer.

I suggest that instead of storing '1,2,3' in one field, you have separate rows for each user the email is being sent to.

edited May 23 '17 at 12:04

Community

1
1

answered Jan 17 '11 at 14:43

gen_Eric

223,194
41
299
337

I was afraid that was going to be the answer. Thank you for your help sir! – castis Jan 17 '11 at 14:50
Yeah, saving a list of IDs in one field isn't the best idea. I learned that as well. You /can/ do it, but it's not the best option. – gen_Eric Jan 17 '11 at 14:57

MySQL multiple keys in a single row

1 Answers1