Message system like facebook - grabbing 'to' and 'from' and then grouping

[dannyb785]

I have a table `Message` and each new message sent will be a new row, but when viewing the inbox, I only want the latest message to show and I only want one row for each unique user that a message has been exchanged with, here's the queries I've tried:

 

SELECT m_date,m_text,m_id,m_from,m_to FROM Message WHERE (m_to='4' OR m_from='4') ORDER BY m_id DESC
// this is for user with id=4

this code grabs all messages that have been sent to or from user=4. Not very helpful

 

SELECT m_date,m_text,m_id,m_from,m_to FROM Message WHERE (m_to='4' OR m_from='4') GROUP BY m_from ORDER BY m_id DESC
// this is for user with id=4

This code is a little better but if userx and usery have both sent at least 1 message to each other, this query will return 2 rows, each row representing the message from the user.

 

As an extra problem, I want to do a JOIN where I get the username from the `User` table but I can't just say "WHERE User.user_id=Message.m_from" because the value in the m_from column might be myself, and I just want it to show the OTHER person's username, whether I sent the message to them or if I got the message from them.

 

How can I do this? Please please please nobody just say "Look into JOIN" because I know how to do joins but this is pretty complicated 

[Barand]

To get both usernames

SELECT f.username as fromname, t.username as toname
FROM message m
INNER JOIN user f ON m.m_from = f.user_id
INNER JOIN user t ON m.m_to = t.user_id

[dannyb785]

  Quote

To get both usernames

SELECT f.username as fromname, t.username as toname
FROM message m
INNER JOIN user f ON m.m_from = f.user_id
INNER JOIN user t ON m.m_to = t.user_id

 

This is only part of what I asked. Do you not know about my other question?

[Barand]

The other part needed a bit more time.

 

When you pay for my time you get the right to complain. At the moment your on my (free) time. So as for the other part, use a subquery.

[ignace]

  Quote

This is only part of what I asked. Do you not know about my other question?

 

  Quote

So as for the other part, use a subquery.

 

See, now your just getting on his bad side.. As that is pretty much a nice FU

[Barand]

Just so we know what you wanted, if you had this data

 

+------+------------+-------------+--------+------+

| m_id | m_date    | m_text      | m_from | m_to |

+------+------------+-------------+--------+------+

|    5 | 2012-07-26 | OK          |      2 |    4 |

|    1 | 2012-07-25 | Hello      |      1 |    3 |

|    4 | 2012-07-24 | Wotcha      |      1 |    2 |

|    3 | 2012-07-23 | Hi          |      1 |    2 |

|    2 | 2012-07-20 | How are you |      2 |    3 |

|    6 | 2012-07-18 | LOL        |      5 |    3 |

|    7 | 2012-07-17 | ROFL        |      4 |    3 |

+------+------------+-------------+--------+------+

 

is this the result you would expect to see?

 

+------+------------+--------+--------+--------+

| uid  | date      | m_text | From  | To    |

+------+------------+--------+--------+--------+

|    1 | 2012-07-25 | Hello  | User A | User C |

|    2 | 2012-07-26 | OK    | User B | User D |

|    3 | 2012-07-25 | Hello  | User A | User C |

|    4 | 2012-07-26 | OK    | User B | User D |

|    5 | 2012-07-18 | LOL    | User E | User C |

+------+------------+--------+--------+--------+

[fenway]

You could join twice back to users, too.