Help with Query to return all messages between 2 users

Hi,

Im trying to create a small little messaging system between users on my site.

I currently have a table - tblMessages - that whenever a message is sent the data gets inserted into.

The table structure is like this:

message_id sender_id receiver_id message_content message_date
1 100 120 return this
2 120 100 and this
3 150 170 not this

I am new to SQL and struggling to think of a query that would return all of the messages between 2 users and ordered by message_date desc. For example just return any messages sent between the users with ID's 100 and 120.

Any help would be greatly appreciated

SELECT 
    *
FROM
    tblMessages
WHERE
    sender_id IN(100,120)
    AND receiver_id IN (100,120)
ORDER BY
    message_date DESC;
1 Like

Thank you very much