CODEWITHSUNDEEP
phpmysqldatabasejoinunion
BenM
BenM

Reputation: 53198

MySQL Data from 3 tables

I have three MySQL tables which relate to a messaging system. The schema and sample data is shown below for each table relating to my question:

`messages`:
+----+---------+----------+
| id | subject | senddate |
+----+---------+----------+
| 1  | Testing | 12344555 |
+----+---------+----------+

`message_senders`:
+------------+---------+---------+
| message_id | user_id | trashed |
+------------+---------+---------+
|      1     |    1    |    1    |
+------------+---------+---------+

`message_recipients`:
+------------+---------+------+----------+---------+
| message_id | user_id | type | readdate | trashed |
+------------+---------+------+----------+---------+
|      1     |    1    |  to  | 12344555 |    1    |
+------------+---------+------+----------+---------+
|      2     |    1    |  cc  | 12344555 |    1    |

My question is how would I select all messages sent by or received by a user, where the trashed parameter is set to 1, without selecting duplicate messages. For example, consider the following scenario:

I want to get the message IDs for all messages trashed by user_id 1, but I don't want to retrieve duplicate IDs (in the data above for example, user_id 1 is the sender AND recipient of message_id 1. I don't want to return the message_id of 1 twice, but want to get all messages for that user.

I think I need to use a combination of JOIN and UNION, but my brain isn't functioning after a long day of PHP!

Upvotes: 1

Views: 699

Answers (4)

mjec
mjec

Reputation: 1817

The solution that leaps to my mind is to use a UNIONd subquery:

SELECT DISTINCT * FROM messages WHERE id IN (
    (SELECT message_id FROM message_recipients WHERE user_id=1 AND trashed=1)
  UNION
    (SELECT message_id FROM message_senders WHERE user_id=1 AND trashed=1)
)

Upvotes: 0

Fosco
Fosco

Reputation: 38506

Here you go, give this a shot:

select distinct m.id
from messages m
left join message_senders s on m.id = s.message_id
left join message_recipients r on m.id = r.message_id
where ((s.user_id = 1 and s.trashed = 1) or (r.user_id = 1 and r.trashed = 1))

Upvotes: 0

N.B.
N.B.

Reputation: 14060

Assuming you got the query in your mind - I'll just hint you can use DISTINCT keyword to force the DB not to return duplicates. I am sure you can work it out yourself, because self-conclusions work the best.

Also, a piece of advice - always store dates as datetime or date instead of int. You avoid daylight savings time problems and you can use various date functions provided by MySQL. Dates are saved internally as 4 byte integers, same as int fields.

Upvotes: 0

Corey
Corey

Reputation:

Try this and see if it only returns one row for each message in the messages table...

    select * from messages 
left join message_senders on messages.id = message_senders.message_id 
left join message_recipients on messages.id = message_recipients.message_id 
where message_senders.trashed = 1 or message_recipients.trashed = 1 and messages.user_id = <value>

Upvotes: 1

Related Questions