CODEWITHSUNDEEP
mysql
Rehan
Rehan

Reputation: 39

List all users who sent or received messages

I have a table "messages".

Fields are:

- id (primary key, int, auto increment)
- uid (the user who sent the message)
- rid (the user who received the message)
- text (text of the message)
- time (time of message)
- read (read or unread. 0 is unread, 1 is read).

Speaking as a user, I can either be UID or RID. So if I want to show a list of users I sent/received messages to/from, how can I do it? Plus each user should be displayed only once. Please help. Thanks. :)

Upvotes: 0

Views: 158

Answers (2)

Ravi
Ravi

Reputation: 31407

I assume, your table design looks like this,

ID | UID | RID |TEXT |TIME | READ

So, i believe, according to your table design, you can use this

SELECT * FROM messages WHERE rid = 'me' OR uid='me'

Upvotes: 0

raina77ow
raina77ow

Reputation: 106385

One possible way to do it is using UNION (not UNION ALL) so that duplicate rows will be removed:

SELECT uid FROM messages WHERE rid = 'me'
UNION
SELECT rid FROM messages WHERE uid = 'me'

Upvotes: 2

Related Questions