Show Message List in SQL

Question

I want to show the message list like facebook messenger where in left corner you can see all the message you make.

user_table

user_id
user_firstname
user_latsname

message_table

message_id
message_user_id
message_to_user_id
message_content
message_time_stamp

My SQL

SELECT user_table.user_id,
             CONCAT(user_table.user_firstname,' ',user_table.user_lastname) AS user_fullname,
             message_table.message_user_id,
             message_table.message_content,
             message_table.message_time_stamp
      FROM user_table
      INNER JOIN message_table
      ON user_table.user_id = message_table.message_user_id
      WHERE (message_table.message_to_user_id = '$session')
      ORDER BY message_table.message_time_stamp DESC

My code works fine if someone message me first. It show in my message list that someone message me. But when i'm the one who make the first message. It doesn't show in my message list. He should reply in my message before i can see it in my message list. But i want to show when someone message me and when i'm the one who make the first message.

message_table with sample data

user_table with sample data

result

But when i add new user in the user_table and message him. it will not show in my Message List.

I want my expected result is when i message new user it will show in my message list

Answer 1

SOLUTION

SELECT user_id, user_fullname, message_content
FROM
(
        (SELECT user_table.user_id,
                 CONCAT(user_table.user_firstname,' ',user_table.user_lastname) AS user_fullname,
                 message_table.message_user_id,
                 message_table.message_content,
                 message_table.message_time_stamp
          FROM user_table
          INNER JOIN message_table
          ON user_table.user_id = message_table.message_to_user_id
          WHERE (message_table.message_user_id = '$session')

        )ORDER BY message_time_stamp DESC

UNION DISTINCT

        (SELECT user_table.user_id,
                 CONCAT(user_table.user_firstname,' ',user_table.user_lastname) AS user_fullname,
                 message_table.message_user_id,
                 message_table.message_content,
                 message_table.message_time_stamp
          FROM user_table
          INNER JOIN message_table
          ON user_table.user_id = message_table.message_user_id
          WHERE (message_table.message_to_user_id = '$session')

         )ORDER BY message_time_stamp DESC

) as id GROUP BY user_id ORDER BY message_time_stamp DESC

Here's the solution what i made in my problem. I made 2 select statement where the first select only select where i'm the one who makes the first message and the second select statements select where someone first message me. And then i Union it.