CODEWITHSUNDEEP
mysql
Sujith
Sujith

Reputation: 17

how to select the last text from message table

I have two tables (messages and user). I want to select the last (msg_id,text) from the messages table for a particular ad_id and need to select the name of the user from the user table. 

SELECT u.id
     , m.date
     , m.ad_id
     , max(m.msg_id)as msg_id
     , u.first_name
     , m.text
     , m.u_to_id
     , m.u_from_id 
FROM user u 
JOIN messages m
  ON CASE WHEN m.u_from_id ='14' THEN u.id = m.u_to_id 
          ELSE u.id = m.u_from_id END 
 AND (m.u_from_id='14' OR m.u_to_id='14') 
 AND m.ad_id='20'  
GROUP BY CONCAT(m.ad_id,u.id)   
ORDER by m.msg_id DESC

this query is working but I can't select t the last m.textTable structure

Upvotes: 0

Views: 123

Answers (2)

Sujith
Sujith

Reputation: 17

After so many experiments added a new column(bargainer) for identify the recipient and this query working fine for me

select m.msg_id,m.text,m.status,m.date,m.bargainer,m.ad_id,u.first_name,u.id from user u    JOIN   messages m  where msg_id in (select max(msg_id) from messages m where m.ad_id=20 and u.id=m.bargainer group by(m.bargainer))group by(m.msg_id) order by msg_id DESC

Upvotes: 0

Ryan Tuosto
Ryan Tuosto

Reputation: 1951

SELECT u.id, m.text
FROM user u
JOIN messages m ON m.msg_id = (SELECT max(msg_id) FROM messages WHERE u_from_id = u.id)

I simplified your query to show the logic relevant to your question. Basically you want to join your messages table on the msg_id that is equal to the inner query of the max msg_id with that user.

Upvotes: 1

Related Questions