| Need help on Trees query!!! Urgent!!! [message #372750] |
Tue, 06 March 2001 21:57 |
Simadak
Messages: 1
Registered: March 2001
|
Junior Member |
|
|
Hi,
I have this table, whose structure is:
msg_no number
thr_no number
msg_desc varchar2
p_date date
user_no number
parentmsg_no number
where parentmsg_no and msg_no have a parent child relationship .
I am using the connect by prior to get the data in a herarchial format.
The query i wrote is:
(select mes.msg_no msgid,mes.p_date p_date,mes.parentmsgno,
(select usr.firstname || ' ' || usr.lastname
from users usr where usr.userno = mes.userno) author,
(select thread.name from threads thread where thread.thr_no =
mes.thr_no) name
from messages mes
where mes.thrno = 301
and trunc(p_date) between
(next_day(to_date(trunc(sysdate))-14,'MON'))
and ((next_day(to_date(trunc(sysdate))-14,'MON')) + 6)
connect by prior msgno = parentmsgno
start with parentmsgno = 0)
THis query returns all the data limited by the date criteria.
Since each msg may or may not be associated with a parentmsgno, for all the messages that have parentmsgnos, I need to display the data of each parent as well until the top parent is reached.
ie, the parents of the related children should not be restricted by the date criteria.
how can i do this using sql?
Please Help!!!
Thanks,
simadak
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
