Link to home
Start Free TrialLog in
Avatar of MeridianManagement
MeridianManagementFlag for United States of America

asked on

mysql query messages and replies to the same topic

I have table described below of messages. I want to display all messages initiated by a specific user, as well as all other messages that replied to the same topic as that message. Then I would need that sorted by date. So essentially, it creates a list of messages, and responses to those messages (so users can see what they wrote, and what people responded after them).

What it is essentially excluding is, messages written by other users on topic the subject user is not participating in, or messages written by other users on a topic before the subject user has participated.

I've attempted what I think the query might look like
messages table
------------------
message_id
topic_id
user_id (person who created the message)
date
 
attempted query
-------------------
select * from messages 
where user_id = '14'
or where exists (select * from messages as messages2 where messages.topic_id = messages2.topic_id and user_id != '14' and messages2.date > messages.date)

Open in new window

Avatar of waygood
waygood

How are you distinquishing which message is a start of a topic? as you've only provided info on the messages.
Do you have a topic table too? as placing the person who started the topic in this table also would be easier
ASKER CERTIFIED SOLUTION
Avatar of MeridianManagement
MeridianManagement
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial