MySQL Forums
Forum List  »  Newbie

Re: two different users join
Posted by: Peter Brawley
Date: July 23, 2021 02:33PM

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.

Options: ReplyQuote


Subject
Written By
Posted
July 23, 2021 11:10AM
Re: two different users join
July 23, 2021 02:33PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.