SQL Duplicate result messages

Question

I have SQL query:

Here is code which I actually using:

SELECT m.message_id, m.message_subject, m.message_message, m.message_smileys, m.message_datestamp, u.user_id, u.user_name, u.user_avatar
            FROM ".DB_MESSAGES." m
                LEFT JOIN (
                    SELECT user_id, user_name, user_avatar 
                    FROM ".DB_USERS." 
                ) u ON m.message_from=u.user_id

            WHERE message_folder='0' AND ((message_to='".$userdata['user_id']."' AND message_from='2')
            OR (message_to='2' AND message_from='".$userdata['user_id']."'))
            ORDER BY message_datestamp

Result:

Result Image

What I need:

Needed Result Image

Database: prntscr.com/9vm1s4

Example:
me: Hello 12:10 AM
he: Hi 12:20AM
he: Have are you ? 12:22AM
me: Fine 1:10PM

Any advice?

Well thank you

This should answer your question (possible duplicate): http://stackoverflow.com/questions/3128043/limit-results-on-sql-request — Topological Sort, Jan 27 '16 at 20:31
A more informative reply would, well, provide more information. Help us help you. — Topological Sort, Jan 28 '16 at 03:51
DB: [link](http://prntscr.com/9vm1s4) I'ts simple chat based on PHP Fusion. I need show mesages from me to him and from him to me ordered by time. Example: **me:** Hello 12:10 AM, **he:** Hi 12:20AM, **he:** Have are you ? 12:22AM, **me:** Fine 1:10PM — Marek Brutus Gajdoš, Jan 28 '16 at 14:04
Welcome to SO. Your question is getting down-voted because it doesn't include enough useful detail. Please take a moment to read these two links, then consider editing your question if you're still looking for help. https://stackoverflow.com/help/how-to-ask and, regarding images, https://meta.stackoverflow.com/a/285557/5790584 — Eric Brandt, Jun 29 '18 at 20:41

score 0 · Answer 1 · answered Jan 27 '16 at 18:39

0

Try to add GROUP BY m.message_from before the ORDER BY part

answered Jan 27 '16 at 18:39

Mahmoud Tantawy

713
5
12

1

That would result in an error, since the select list includes other columns. At least, it would in SQL Server. – Tab Alleman Jan 27 '16 at 18:42
Now I get only 2 resaults ... but I need all from DB – Marek Brutus Gajdoš Jan 27 '16 at 18:44
OP can easily solve this by adding the column to the `select` – Mahmoud Tantawy Jan 27 '16 at 18:45
@MarekBrutusGajdoš in the "needed" image there is only 2, explain more what you need please – Mahmoud Tantawy Jan 27 '16 at 18:46
When I making "needed" image. I removed duplicated messages with inspector. In my DB is many messages and all of message have sender ID and reciver ID Its my full resault: [link](http://prntscr.com/9vlxqs) – Marek Brutus Gajdoš Jan 27 '16 at 18:51
So you want all messages of a user to be returned but grouped together? – Mahmoud Tantawy Jan 27 '16 at 18:53
If that's the case then add to `ORDER BY` the `m.message_from`, this way all messages for user X will be returned after each other THEN all messages for user Y, etc ... – Mahmoud Tantawy Jan 27 '16 at 18:54
DB: [link](http://prntscr.com/9vm1s4) I'ts simple chat based on PHP Fusion. I need show mesages from me to him and from him to me ordered by time – Marek Brutus Gajdoš Jan 27 '16 at 18:59

score 0 · Answer 2 · edited Jun 29 '18 at 20:32

PHP Fusion add message to DB duplicated.

Here is the code which I am actually using:

SELECT m.message_id, m.message_subject, m.message_message, m.message_smileys, m.message_datestamp, u.user_id, u.user_name, u.user_avatar
            FROM ".DB_MESSAGES." m
                LEFT JOIN (
                    SELECT user_id, user_name, user_avatar 
                    FROM ".DB_USERS." 
                ) u ON m.message_from=u.user_id

            WHERE message_folder='0' AND ((message_to='".$userdata['user_id']."' AND message_from='2')
            OR (message_to='2' AND message_from='".$userdata['user_id']."'))
            ORDER BY message_datestamp

SQL Duplicate result messages

2 Answers2