Re: two different users join

[Peter Brawley]

Remember, SQL is about sets ... retrieve all data for conversation_id=1 with ...

select c.*, m.*
from conversations c
join messages m using(conversation_id)
where c.conversation_id=1
order by m.message_id;

About your DDL ...

1 You might have arrived at your query sooner if you'd added ...

FOREIGN KEY(conversation_id) REFERENCES conversations(conversation_id)

... to the messages table.

2 Especially for maintenance, names should self-document as much as possible. Your ` created_date` column name misleads, the column is a timestamp not a date; call it created_ts or something like that.



Edited 1 time(s). Last edit at 07/23/2021 02:34PM by Peter Brawley.