sql - Recursive query in Oracle

Question

I am kind of new to the more advanced topics of PLSQL, so hopefully someone can help me out.

The problem: I have a table with messages sent between an admin and users. The table has a message_parent with FK to the same table message_id field: in case the field is populated, then it means that message was sent as a reply to a previous message. I need to select all the messages that are part of the same conversation and display them. Can this be done with a single query or do I need a procedure to handle that kind of logic? As I understand, it needs to be recursive, since the message_id by which I am searching, is always changing

Example Messages table:

|message_id|parent_id|message_content|
|----------|---------|---------------|
|101       |100      | foo           |
|100       |97       | bar           |
|99        |(null)   | Left out      |
|97        |(null)   | baz           |

So the correct query selecting message_content should return "baz", "bar" and "foo" but not "Left out" (since baz is the original message). This would be simple if there were e.g. only two messages that can be tied together or e.g. a thread_id column, that would link all messages in the same 'thread', but with the parent_id's constantly shifting, I am having trouble figuring it out.

Answer 1

In Oracle this is easily done using CONNECT BY

select message_id, parent_id, message_content
from messages
start with message_id = 97 -- this is the root of your conversation
connect by prior message_id = parent_id;

This walks the tree from top to bottom.

If you want to walk the tree from a single message to the root, change the start with and the connect by part:

select message_id, parent_id, message_content
from messages
start with message_id = 100 -- this is the root of your conversation
connect by prior parent_id = message_id; -- this now goes "up" in the tree