Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

parent/child records from same table

606667Apr 19 2013 — edited Apr 22 2013
I want to create a query that is a union such that the 2nd resultset is based on the 1st resultset. I have a table that has parent/child records in the same table.

Table: EVENTS
EVENT_ID
PARENT_EVENT_ID
CREATED_DATE
(other columns)

if PARENT_EVENT_ID is null then it is a parent record, else it is a child record. I want to select all parent records then union them with all the associated child records...something like this:

select * from EVENTS where CREATED_DATE < sysdate - 90 and PARENT_EVENT_ID is null -- All parents
union
select * from EVENTS where PARENT_EVENT_ID in (select EVENT_ID from EVENTS where CREATED_DATE < sysdate - 90 and PARENT_EVENT_ID is null) -- include any children of parents selected from above

This works but it's kind of ugly, I want to avoid using the sub-select in the 2nd because it is a repeat of the 1st statement, is there a way to alias the first statement and just refer to it in the 2nd query?
This post has been answered by Frank Kulash on Apr 19 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 20 2013
Added on Apr 19 2013
10 comments
6,480 views