Re: Help writing SQL query

> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1102349402.953970_at_yasure...
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
>
> barry wrote:
>
> 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.

Thank you for your reply Daniel but it does not help me. Further I am neither a school child nor need to understand anything about normalisation. This is the structure of the information I have and I need some help writing the SQL query to give me the required resultset. I do not believe this is as you put it trivial. Repost below.

Original message follows:

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 Mon Dec 06 2004 - 11:51:22 CST