| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Outer Join Clarification?
On Wed, 28 Jul 1999 20:36:52 GMT, Martin Douglas <Martin.Douglas_at_Boeing.com>
wrote:
>I would be grateful if someone could clarify the correct result of the
>following SQL statements for me....
>
>My situation:
>
>A simple hierarchical tree... (sibling order does not matter)
>
> A
> > B
> > C
>
>Internally stored (cannot assume the order) as...
>
> TD_SELF TD_BASELINE TD_NAME
> ------- ----------- -------
> 10 null A
> 30 10 B
> 20 10 C
>
>Desired resultset...
>
> Parent ID Parent Name Child ID Child Name
> --------- ----------- -------- ----------
> null null 10 A
> 10 A 30 B
> 10 A 20 C
>
>or alternatively...
>
> Parent ID Parent Name Child ID Child Name
> --------- ----------- -------- ----------
> null null 10 A
> 10 A 20 C
> 10 A 30 B
>
How about just doing ...
SQL> select * from t;
TD_SELF TD_BASELINE TD_NAME
---------- ----------- -------
10 A
20 10 B
30 10 C
SQL>
1 select prior td_self "Parent ID",
2 prior td_name "Parent Name", 3 td_self "Child ID", 4 td_name "Child Name" 5 from T
10 A
10 A 20 B
10 A 30 C
This is all you should need. Oracle will take care of putting the parent first so long as you do not put an order by on it.
hope this helps.
chris.
>SQL Statement #1...
>
> SELECT * FROM
> (
> SELECT TD_SELF AS "Parent ID", TD_NAME AS "Parent Name" FROM
>TEMPLATE_DEFINITION
> ) A,
> (
> SELECT TD_SELF AS "Child ID", TD_BASELINE, TD_NAME AS "Child
>Name" FROM TEMPLATE_DEFINITION
> START WITH TD_SELF IN(SELECT TD_SELF FROM TEMPLATE_DEFINITION
>WHERE TD_BASELINE IS NULL)
> CONNECT BY TD_BASELINE = PRIOR TD_SELF
> ) B
> WHERE
> A.TD_SELF(+) = B.TD_BASELINE;
>
>Outcome of this statement yields one of the desired resultsets.
>
>SQL Statement #2...
>
> SELECT
> TD1.TD_SELF AS "Child ID",
> TD1.TD_BASELINE AS "Parent ID",
> TD2.TD_NAME AS "Parent Name",
> TD1.TD_NAME AS "Child Name"
> FROM
> TEMPLATE_DEFINITION TD1,
> TEMPLATE_DEFINITION TD2
> WHERE
> TD1.TD_SELF IN (
> SELECT TD_SELF FROM TEMPLATE_DEFINITION
> START WITH TD_SELF IN(SELECT TD_SELF FROM TEMPLATE_DEFINITION
>WHERE TD_BASELINE IS NULL)
> CONNECT BY TD_BASELINE = PRIOR TD_SELF
> )
> AND
> TD2.TD_SELF(+) = TD1.TD_BASELINE;
>
>Outcome of this statement is undesired!
>
> Parent ID Parent Name Child ID Child Name
> --------- ----------- -------- ----------
> 10 A 20 C
> 10 A 30 B
> null null 10 A
>
>My application requires that the record for the parent preceed all
>records for the parent's children.
>
>Question 1) Does the first SQL statement guarantee that the parents
>will always preceed their respective children? If so, how does Oracle
>know that the order should be dominated by the second table subquery (B)
>over the first one (A)?
>
>Question 2) In the second SQL statement, is the reason for the
>undesired order that the subquery in the where clause is simply
>filtering the result of the outer join between TD1 and TD2? And thus
>the order is really defined by the outer join. If so, is the order
>resulting from the outer join random as far as Oracle is concerned?
>
>###
>
>Much thanks ahead of time to you true experts :)
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |