Re: Question on Hierarchy

ronnie_yours_at_yahoo.com (Ronnie) wrote in message news:<ea603f8d.0308191244.a771d15_at_posting.google.com>...
> Hi,
>
> I have a table which has data on father and sons
>
> SQL> desc father_sons
> Name Null? Type
> ----------------------------------------- -------- ------------
> FATHER VARCHAR2(1)
> SON VARCHAR2(1)
>
> The data in the table looks something like this
>
> SQL> select * from father_sons;
>
> F S
> - -
> a b
> b c
> c d
>
> where b is the son of a
> c is the son of b
> d is the son of c
>
> Lets say I were given "a" and "d" and asked to find out whether they
> are related or not. In this case they are.
>
> How do i write a query which just gives me true or false on whether
> they are related or not.
>
> Please suggest
>
> Thanks
> Ron

Ron, I am not at a location where I can test my idea but if I had to solve this I would probably write a user defined function (stored plsql) that accepted A and B as input returning the string True | False.

I would use a connect by clause to generate the dependents of A and check to see if B was in it. If yes, return True otherwise use a second connect by clause to drive up the parent chain to see if B is in it. If B is found then return True else return False.

A second ago I had an idea using an exists clause but without a system in front of me it escaped. Someone else may post a better approach.

HTH -- Mark D Powell -- Received on Tue Aug 19 2003 - 20:47:39 CDT