ordering by another table column mysql

Question

I have two tables, messages and thread messages is the main table and thread is where I keep the conversation between two users. I want to select only from messages table and order by thread_id from thread table. I am using the below sql but it doesn't order as I want it.

My aim is when new message is inserted to thread table make the parent message in messages table appear at the top. Does anyone have any idea?

SELECT m.* 
FROM messages AS m 
     JOIN thread AS t 
          ON t.thread_id = m.id 
WHERE ( m.to_user = ?  
    AND m.deleted != ?
    AND m.del2 != ?)
    OR
    (m.from_user = ? 
     AND m.conversation = 'true'
     AND m.deleted != ? 
     AND m.del2 != ?) 
ORDER BY t.thread_id desc);

messages table

id      to_user  from_user      message      is_read     deleted     del2   conversation
----------------------------------------------------------------------------------------
1        user1     user2         hi mark,      true                         true
2        user3     user4         wass up yo?   true                         true
3        user1     user3         blah blah     

thread table

thread_id      thread_to_user  thread_from_user      thread_message     thread_message_id  thread_is_read
---------------------------------------------------------------------------------------------------

1              user2           user1               hi there,                    1            1
2              user1           user2               hey, wassup                  1            1
3              user2           user1               not much, hw u doin          1            1
4              user1           user2               doing great and you?         1            0
5              user3           user4               heyyyy                       2            1
6              user4           user3               hi, u coming?                2            0

What I am expecting when echoing the row:

id      to_user  from_user      message      is_read     deleted     del2   conversation
----------------------------------------------------------------------------------------
 2        user3     user4         wass up yo?   true                         true
 1        user1     user2         hi mark,      true                         true
 3        user1     user3         blah blah     

Answer 1

Try this one:

SELECT DISTINCT(id), to_user,from_user,
 message, is_read, conversation
FROM messages
LEFT JOIN (
  SELECT thread_id, thread_message_id
  FROM messages m
  LEFT JOIN thread t
  ON m.id = t.thread_message_id
) tbl
ON messages.id = tbl.thread_message_id
ORDER BY tbl.thread_id DESC

See fiddle demo

You could put your WHERE inside the subquery, like:

 SELECT DISTINCT(id), to_user,from_user,
  message, is_read, conversation
  FROM messages
  LEFT JOIN (
   SELECT thread_id, thread_message_id
   FROM messages m
   LEFT JOIN thread t
   ON m.id = t.thread_message_id
   WHERE ( m.to_user = ?  
   AND m.deleted != ?
   AND m.del2 != ?)
   OR
  (m.from_user = ? 
    AND m.conversation = true
    AND m.deleted != ? 
    AND m.del2 != ?) 
  ) tbl
  ON messages.id = tbl.thread_message_id
  ORDER BY tbl.thread_id DESC

See Fiddle Demo with WHERE

Update (Using MAX() with subquery)

SELECT id, to_user, from_user, message, is_read, conversation
FROM (
 SELECT m.id, max(t.thread_id) thread_id, m.to_user,m.from_user,m.message, m.is_read,    m.conversation
 FROM messages m
 LEFT JOIN thread t on(t.thread_message_id = m.id)
  WHERE ( m.to_user = 'user1'  
  AND m.deleted != true
 AND m.del2 != true)
 OR
 (m.from_user = 'user3' 
  AND m.conversation = true
  AND m.deleted != true 
  AND m.del2 != true) 
 GROUP BY m.id
 ORDER BY thread_id desc
 ) tbl

See Other Demo