Barry, it seems that you should be able use a stored procedure with a
recursive function. Maybe some of the more savvy chaps here who know sqlplus
can help you.
JCD
"barry" <bbulsara23_at_hotmail.com> wrote in message
news:747f1dec.0412060344.7116e3fb_at_posting.google.com...
> Hi, I have inherited some information from an Access database in a
> terrible structure . All I want an SQL query, any version or Oracle,
> to return anyone in the same family as Mary, or Bob or, to Brian, or
> Beatrice, whoever...
>
> ie, SELECT personName
> FROM family
> WHERE IN SAME FAMILY AS 'Brian'
>
> would return Mary, Bob, Joan, Brian, Peter, Claudine, Bruce, and
> Francis but no one else.
>
> SELECT *
> FROM personName
> WHERE IN SAME FAMILY AS 'Claudine'
>
> should return the same results
>
> SELECT *
> FROM personName
> WHERE IN SAME FAMILY AS 'Dominic'
>
> should return Dominic and William and no one else.
>
>
> Can you help ? Sample family data is below.
>
> Thank you
> Barry
>
>
>
> CREATE TABLE family(familymember1 VARCHAR2(20),familymember2
> VARCHAR2(20));
>
> INSERT INTO family(familymember1,familymember2)
> VALUES('Mary','Bob');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Mary','Joan');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Brian','Mary');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Peter','Brian');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Peter','Mary');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Mary','Brian');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Brian','Claudine');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Claudine','Bruce');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Francis','Bruce');
>
> INSERT INTO family(familymember1,familymember2)
> VALUES('John Francois','Barbara');
> INSERT INTO family(familymember1,familymember2)
> VALUES('John Francois','Beatrice');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Jean-Claude','Barbara');
>
> INSERT INTO family(familymember1,familymember2)
> VALUES('Dominic','William');
>
> INSERT INTO family(familymember1,familymember2)
> VALUES('Elizabeth','Margaret');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Elizabeth','Philip');
> INSERT INTO family(familymember1,familymember2)
> VALUES('Fergie','Philip');
Received on Tue Dec 07 2004 - 08:28:33 CST
