2

I have two mysql tables Users & Messages. Please will you help me to create an sql query which returns only the most recent message either sent or received by the logged in user Mike ( user_id=1 ) along with the message_id, message_text & first_name of the person who the message was sent to or received from. Ordered by message_id in descending order.

So far i have the query:-

SELECT message_id, first_name, message_text FROM tbl_users, tbl_messages 
WHERE 
(tbl_users.user_id = tbl_messages.sender_id AND tbl_messages.receiver_id = 1 
OR tbl_users.user_id = tbl_messages.receiver_id AND tbl_messages.sender_id = 1) 
GROUP BY tbl_users.first_name
ORDER BY message_id DESC

Which gives the result in the image below. It seems the ORDER BY is being ignored

Group by result

Thank you in advance

tbl_users

---------------------------
| user_id |  first_name   |
---------------------------
| 1       |  Mike         |
| 2       |  John         |
| 3       |  George       |
| 4       |  Peter        |
| 5       |  Sarah        |
---------------------------

tbl_messages

----------------------------------------------------------------
| message_id |  sender_id   |  receiver_id   |  message_text   |
----------------------------------------------------------------
| 1          |  2           | 1              |  Hello          |
| 2          |  3           | 1              |  How are you    |
| 3          |  1           | 5              |  Hi there       |
| 4          |  2           | 1              |  Greetings      |
| 5          |  1           | 4              |  Good day       |
| 6          |  3           | 1              |  Hi             |
| 7          |  5           | 1              |  A message      |
| 8          |  5           | 4              |  Good morning   |
| 9          |  1           | 5              |  Hello dear     |
| 10         |  1           | 3              |  Howdy          |
----------------------------------------------------------------

Desired result

----------------------------------------------
| message_id |  first_name  |  message_text  |
----------------------------------------------
| 10         |  George      | Howdy          |
| 9          |  Sarah       | Hello dear     |
| 5          |  Peter       | Good day       |
| 4          |  John        | Greetings      |
----------------------------------------------

Database is available here

Improve this question
3
  • do you mean recent message to or from person X? And result table doesn't tell has Mike sent or recieved message? Commented Dec 5, 2012 at 16:29
  • Possible duplicate: stackoverflow.com/questions/5510034/… Commented Dec 5, 2012 at 16:41
  • Messages with id 10, 9 5 and 3 were sent by Mike to George, Sarah & Peter. Mike's name should never be in the results because he's the logged in user. It's like how the chat list is displayed on WhatsApp Commented Dec 5, 2012 at 16:41

4 Answers 4

Reset to default
3

Just adding a GROUP BY clause may solve your problem, try this...

SELECT message_id, first_name, message_text FROM tbl_users, tbl_messages 
WHERE  
(tbl_users.user_id = tbl_messages.sender_id AND tbl_messages.receiver_id = 1 
OR tbl_users.user_id = tbl_messages.receiver_id AND tbl_messages.sender_id = 1) 
GROUP BY tbl_users.user_id
ORDER BY message_id DESC

UPDATE: Changing the whole query

SELECT t2.message_id, t2.first_name, t2.message_text FROM
(SELECT u.user_id, m.message_id, u.first_name, m.message_text FROM `tbl_users` u
  JOIN `tbl_messages` m ON (u.user_id = m.sender_id AND m.receiver_id = 1)
    OR (u.user_id = m.receiver_id AND m.sender_id = 1) 
  ORDER BY m.message_id DESC
) t2
GROUP BY t2.user_id
ORDER BY t2.message_id DESC

RESULT:

+------------+------------+--------------+
| message_id | first_name | message_text |
+------------+------------+--------------+
| 10         | George     | Howdy        |
| 9          | Sarah      | Hello dear   |
| 5          | Peter      | Good Day     |
| 4          | John       | Greetings    |
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'll modify the question to show the result when GROUP BY is added
Grouping by tbl_users.first_name gives the closest result just that the first 4 messages are returned instead of the last (most recent) 4. Grouping by multiple columns does not work
@user1715545 - I've updated my whole query and seems to be working fine... Sorry for the late reply...
2

If you GROUP BY the field which you want to de-duplicate on. In this case GROUP BY tbl_users.first_name should do the trick.

Improve this answer

3 Comments

What do you mean exactly? If you GROUP BY tbl_users.first_name it will show the unique first names. If you want to show unique values for all fields, you can GROUP BY multiple fields. Such as, GROUP BY tbl_users.first_name, tbl_message.message_text. This will show unique message/user entries.
Every field you want to be unique needs to be in the GROUP BY.
Grouping by tbl_users.first_name gives the closest result just that the first 4 messages are returned instead of the last (most recent) 4. Grouping by multiple columns does not work
0

You are missing the table name in the ORDER BY clause. Also I suggest using aliases on the tables. Thus, your query might look like this:

/* All Mike's messages */
SELECT
  m.message_id,
  u.first_name,
  m.message_text
FROM
  tbl_users AS u,
  tbl_messages AS m
WHERE  
(
  u.user_id = m.sender_id AND 
  m.receiver_id = 1
) OR (
  u.user_id = m.receiver_id AND 
  m.sender_id = 1
)
ORDER BY
  m.message_id DESC;

However, I think it would be better to get the sent and received messages results separately. So:

/* Messages sent my Mike */
SELECT
  m.message_id,
  u.first_name,
  m.message_text
FROM
  tbl_users AS u
  INNER JOIN tbl_messages AS m ON m.receiver_id = u.user_id
WHERE
  m.sender_id = 1
ORDER BY
  m.message_id DESC;

/* Messages received my Mike */
SELECT
  m.message_id,
  u.first_name,
  m.message_text
FROM
  tbl_users AS u
  INNER JOIN tbl_messages AS m ON u.user_id = m.sender_id
WHERE
  m.receiver_id = 1
ORDER BY
  m.message_id DESC;
Improve this answer

2 Comments

I need the sent and received messages to be retrieved simultaneously so I can all list the conversations which Mike is involved in. Similar to the way chats are listed on WhatsApp. Aliases on table names are great, the result i get when i run your first query is exactly the same as the image in the question, even if i add GROUP BY u.first_name the first result should be message_id=10
I get "10 George Howdy" as my first result
0

I am not sure you can achieve this through join. A sub select query may help you.

  select a.first_name, 
         (select message_text from tbl_messages b
          where (a.user_id = b.sender_id and b.sender_id = 1)
                or (a.user_id = b.receiver_id and b.receiver_id  = 1)
          order by message_id DESC
          LIMIT 1)
   from tbl_users a;
Improve this answer

3 Comments

I'll try this and will let you know
I've tried to run the code exactly as you typed but i'm not getting any results
@user1715545 I made a mistake and put the filter on user_id. Updated the query. Please try and let me know.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.