CODEWITHSUNDEEP
t-sql
fillippefilloppe
fillippefilloppe

Reputation: 33

MERGING TABLE VALUES

I have a single table: comments Columns are: author_id, id, parent_id, timestamp

Example:

author_id, id, parent_id, timestamp
1, 1, NULL, 12:00 << this is the original post
2, 1234, 1, 12:04 << this is a reply made 4 minutes after
3, 5678, 1, 12:05 << this is another reply

All replies share OP's 'id' as their 'parent_id'

What I want is a single table or view, ordered so all the conversations (OP's and replies) are ordered as above. What I have right now is a list of all the comments (OP's and replies) simply ordered by time, so I have lots of overlapping conversations. I need to bond the conversations, but not as a join because it gives a repeat of each OP for each reply and double the columns I need.

Thanks

Upvotes: 1

Views: 33

Answers (1)

momar
momar

Reputation: 364

Assuming that the conversation is linked by id and parent_id and that a new id means the start of a new conversation. You could write something like this:

select
    ISNULL(parent_id, id) as ConversationId,
    *
from 
    Comments
order by ConversationId, timestamp

Upvotes: 1

Related Questions