MySQL – How to Join Tables with Different Values

MySQLPHP

I have 2 tables(as shown by the image below) inbox_messages_table and users_table
enter image description here

So what i am trying to do is combining the 2 tables ON user_id but in my inbox_messages_table i have an id which i created for the website itself which is (-1) to have it unique so how can i do this and be able to retrieve the -1 too with others ?

my query so far is this :

// $user_id is the logged in user

SELECT * FROM inbox_messages_table i 
    JOIN users_table u ON i.from_user_id = u.user_id 
    WHERE u.banned = 0 
        AND to_user_id = $user_id 
        AND deleted = 0 ORDER BY message_timestamp DESC

Best Answer

You can use LEFT JOIN instead of JOIN.

With LEFT JOIN the query will return all records from the left table i.e. the one in the FROM, even if there is no matching record in the joint table.

SELECT * FROM inbox_messages_table i 
LEFT JOIN users_table u ON i.from_user_id = u.user_id 
WHERE u.banned = 0 AND to_user_id = $user_id AND deleted = 0 ORDER BY message_timestamp DESC
Related Question