| Tricky SQL statement - infinite recurstion? [message #374046] |
Tue, 22 May 2001 10:16  |
Brian Flynn
Messages: 2
Registered: May 2001
|
Junior Member |
|
|
Hi folks - got a good thinking problem. It may be a well known problem, but isn't immediately obvious to me and I haven't found it anywhere on the net so I'll throw it out as a question.
I want to generate a message list with infinite recursion. The rough table structure I immediately thought of was:
create table T_TABLE
(
pkey number
text_subject varchar2(200)
text_text varchar2(2000)
num_response_to number
)
The idea is that each message has a pkey and can have a parent message. If it doesn't, then its a root level message; if it lists a parent, it should be grouped with the parent.
Ok, good so far. Remember that this is just a straw dummy. The problem I have is that I don't immediately see how to recognize child messages without creating a bunch of subqueries (everytime you display a message, create a new query which finds children). The problem with that solution of course is the overhead.
So - given the table structure I listed or ANY table structure....how can it be done efficiently? A single query is great, a limited number of queries is acceptable. Something that has unlimited queries probably won't work....
Thinking cap time...
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
