Re: Help writing SQL query

barry wrote:

> 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');

As this seems to be school work my recommendation is that you start off by reviewing the rules of normalization. If the structure is corrected the solution becomes trivial.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)