HANZO SALAH HANZO SALAH - 7 months ago 16
SQL Question

Message discussion query

I try to create a discussion system that display messages between two users ordering by time like the image using SQL, for example if user 10 want to see his messages with user 45, this is my table structure:

Messages( id(PK), sender_id(FK_USER), receiver_id(FK_USER), subject, content,created_at,updated_at);

I tried this sql query but it doesn't show what i want:

select s.id,
s.subjet,
d.source_id as `source`,
d.id,
d.subject
from messages s,
messages d
where s.destination_id=d.source_id
and d.source_id=s.destination_id
and s.source_id=202
order by created_at asc;


Example

Answer

If I understand your table structure and requirement correctly, you can try something like below -

SELECT *
  FROM MESSAGES
 WHERE SENDER_ID IN (10, 45)
   AND RECEIVER_ID IN (10, 45)
 ORDER BY CREATED_AT

Or like this -

SELECT *
  FROM MESSAGES
 WHERE (SENDER_ID = 10 AND RECEIVER_ID = 45)
    OR (RECEIVER_ID = 10 AND SENDER_ID = 45)
 ORDER BY CREATED_AT

by default ordering will be done in ASC fashion so no need to mention that.