CODEWITHSUNDEEP
mysqlselectjoingroup-by
omega
omega

Reputation: 43873

How to use group by with join in mysql?

This is my mysql query:

SELECT t.id,
       t.user_id_1,
       t.user_id_2,
       m.id as message_id,
       m.from_user_id,
       m.text,
       max(m.date_sent),
       m.viewed
FROM   thread t
       JOIN message m 
            ON t.id = m.thread_id
WHERE t.user_id_1 = 1 
      OR t.user_id_2 = 1
GROUP BY t.id

There's two tables, thread and messages. The thread table stores the records (each record is a conversation), and the messages table has the messages. It will have a foreign key to the thread table to indicate which thread the message is in.

I'm trying to get a list of threads, along with the most recent message for each thread. But the above is not working.

What I am trying to do is, it get all the threads, and join it with the messages (in that thread). Then groups the message by thread id and use the record with the most recent date given by the max function.

But instead it gives some random message with the most recent date (The message is from a different record).

Does anyone know how to fix this?

Upvotes: 0

Views: 78

Answers (1)

John Woo
John Woo

Reputation: 263733

You can have a separate subquery that gets the latest date_sent for every thread_id on table message. Since the subquery has only two columns, you need to join the results on the table itself to get the others provided that the it matches on both columns: thread_id and date_sent.

SELECT  t.id, 
        t.user_id_1, 
        t.user_id_2, 
        m.id as message_id, 
        m.from_user_id, 
        m.text, 
        m.date_sent, 
        m.viewed
FROM    thread t
        INNER JOIN message m 
            ON t.id = m.thread_id
        INNER JOIN
        (
            SELECT  thread_id, MAX(date_sent) date_sent
            FROM    message
            GROUP   BY thread_id
        ) s ON  m.thread_id = s.thread_id
                AND m.date_sent = s.date_sent
WHERE   1 IN (t.user_id_1, t.user_id_2)

Upvotes: 2

Related Questions