CODEWITHSUNDEEP
mysqlsql
Champa
Champa

Reputation: 85

Selecting last record from inner joined records

I made a post 6 days ago but no one seemed to anwser correctly and it was eventually abandoned so here I post it again (sorry for DPing but this is important to me) I have 2 tables - people (query is based on people.id so no need to ss the entire table) and messages (http://prntscr.com/94iq2e)

I have a query which is grouping messages with people and that is working fine but each person is grouped with the first message he sent and I need to make it so that it groups people with the LAST message they sent

Here is the query which is grouping people with the FIRST message

SELECT     people.id, 
       people.avatar, 
       people.firstname, 
       people.lastname, 
       LEFT(messages.message, 90) AS message, 
       messages.seen, 
       messages.date 
FROM       people 
INNER JOIN messages 
ON         people.id = messages.sender_id 
WHERE      reciever_id = '". $user_data['id'] ."' 
GROUP BY   sender_id 
ORDER BY   date DESC limit 11

Link to the previous topic -> Selecting last record from INNER JOIN and grouping

Upvotes: 0

Views: 366

Answers (3)

Slasko
Slasko

Reputation: 407

I dont know mysql but in TSQL it would be like this to get last message: 

ISNULL(SELECT TOP 1 messages.message FROM messages WHERE people.id = messages.sender_id ORDER BY messages.id DESC,'')

Whole code would look like this:

 SELECT     people.id, 
   people.avatar, 
   people.firstname, 
   people.lastname, 
   ISNULL(SELECT TOP 1 messages.message FROM messages WHERE people.id = messages.sender_id ORDER BY messages.id DESC,'') AS message, 
   messages.seen, 
   messages.date 
FROM       people 
INNER JOIN messages 
ON         people.id = messages.sender_id 
WHERE      reciever_id = '". $user_data['id'] ."' 
GROUP BY   sender_id 
ORDER BY   date DESC limit 11

Upvotes: 0

Robert Kitching
Robert Kitching

Reputation: 300

I would recommend using a sub query to pull out the latest messages. The below link may of help to you, it details a few possible solutions.

SQL join: selecting the last records in a one-to-many relationship

Cheers, Bob

Upvotes: 0

fthiella
fthiella

Reputation: 49049

You could use a subquery that returns the maximum date:

SELECT sender_id, MAX(date) AS max_date
FROM messages
GROUP BY sender_id

and join people table with this subquery, and then join to the messages table to get the message with the maximum date:

SELECT
   people.id, 
   people.avatar, 
   people.firstname, 
   people.lastname, 
   LEFT(messages.message, 90) AS message, 
   messages.seen, 
   messages.date 
FROM
   people INNER JOIN (
     SELECT sender_id, MAX(date) AS max_date
     FROM messages
     GROUP BY sender_id
   ) lm ON people.id = lm.sender_id
   INNER JOIN messages 
   ON people.id = messages.sender_id AND
      lm.max_date=messages.date
WHERE
   reciever_id = ...
ORDER BY
   ...

Upvotes: 1

Related Questions