CODEWITHSUNDEEP
mysqljoindistinct
Bibudha R Sahoo
Bibudha R Sahoo

Reputation: 65

Trouble getting distinct results in join query in mysql

I have two tables as following:

Mail_Thread
-------------
id,
thread_name,
created

,

Mail
----
id,
thread_id (FK for Mail_thread),
to,
subject,
message,
created

Here one thread contains multiple mails. So I want results to be of distinct thread and last message of that thread using join. So any possibilities ?

Upvotes: 0

Views: 37

Answers (2)

Ankit Agrawal
Ankit Agrawal

Reputation: 2454

select mt.id,mt.thread_name,mt.created
from mail_thread mt 
inner join mail m on mt.id =m.thread_id 
group by m.thread_id
order by mt.created desc

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

You accomplish this with a series of joins in MySQL. The first join connects threads to mail messages, and the second join restricts those mail messages to only the most recent ones for each thread.

SELECT t1.thread_name,
       t2.message
FROM Mail_Thread t1
INNER JOIN Mail t2
    ON t1.id = t2.thread_id
INNER JOIN
(
    SELECT thread_id, MAX(created) AS max_created     -- this subquery identifies
    FROM Mail                                         -- the most recent message
    GROUP BY thread_id                                -- for each thread
) t3
    ON t2.thread_id = t3.thread_id AND
       t2.created   = t3.max_created

Upvotes: 2

Related Questions