SQL to get a particular thread based on two user

Question

I recently build a thread based messaging system with the help from this post Thread Messaging

The 3 tables that involves are

member(member_id, member_userunique, and so on..)
thread_participant(thread_id, member_id)
message(message_id, message_subject, message_body, member_id, thread_id, and so on)

Within the participant table i have the following records

thread_id | member_id
1---------| 959
1---------| 1
1---------| 6
2---------| 959
2---------| 6
3---------| 959
3---------| 1
3---------| 6

And in the message table i have the following record (simplified version)

message_id | member_id | thread_id
1 ---------| 959 ------| 1
2 ---------| 959 ------| 2
3 ---------| 959 ------| 1
4 ---------| 959 ------| 1
5 ---------| 959 ------| 2
6 ---------| 959 ------| 3
7 ---------| 959 ------| 1

Hence we know that member id 959 and 1 are engage in 2 different threads, namely 3 (with message[s] id: 6) and 1 (with message[s] id: 1, 3 ,4 ,7).

However i am stuck with a particular query to get a particular thread based on two users.

So far this is the query that i have modified

SELECT m.message_id
FROM thread_participant AS participant
INNER JOIN message AS m ON participant.thread_id = m.thread_id
WHERE participant.member_id = 1 OR participant.member_id = 959
GROUP BY participant.thread_id

The query above is the short version of this query

SELECT message.message_id,
message.message_title, message.message_body,
message.message_sent_date,
user.member_userunique
FROM message AS message
INNER JOIN member AS user ON message.message_author = user.memberid
WHERE
(
    message.message_id IN
    (
        SELECT m.message_id
        FROM thread_participant AS participant INNER JOIN message AS m
        ON participant.thread_id = m.thread_id
        WHERE (participant.member_id IN (1,959))
        GROUP BY participant.thread_id
    )
)
AND message.message_title != 'UNDEFINED'
ORDER BY message.message_sent_date DESC

Note: The last query i am using the IN clause instead of the OR clause. There are more tables being used such as the Thread table which contains thread_id and creation_date.

Could anyone help me with this? Thx

score 0 · Accepted Answer · answered Jun 18 '13 at 10:02

Quick attempt (and not tested), but maybe using joins:-

SELECT a.message_id,
    a.message_title, a.message_body,
    a.message_sent_date,
    z.member_userunique
FROM message a
INNER JOIN thread_participant b ON a.thread_id = b.thread_id AND b.member_id = 1
INNER JOIN thread_participant c ON a.thread_id = c.thread_id AND c.member_id = 959
INNER JOIN member z ON a.message_author = z.memberid
ORDER BY a.message_sent_date DESC

Select from the message table any message from a thread which has both specified members as a participant, joining back against the member table to get the details on the author of any message.

thanks for your solution, with a little adjustment it works as i want it. Thanks heaps. — Jeremy, Jun 18 '13 at 10:40

SQL to get a particular thread based on two user

1 Answers1