how to make this join

From: YuHong Zhang <yhzhang_at_astro.ocis.temple.edu>
Date: Fri, 14 Jan 1994 02:53:01 GMT
Message-ID: <1994Jan14.025301.15990_at_cronkite.ocis.temple.edu>

Dear SQL and ORACLE experts:

I have table named relation which has following data:

        ID RELATION           PROBAND      FAMID     MOTHER     FATHER          
---------- --------------- ---------- ---------- ---------- ----------          
      1140 MOT_BRO               1128         71       1134       1135          
      1141 MOT_BRO               1128         71       1134       1135          
      1148 FAT_SIS               1128         71       1146       1147          
      1149 FAT_BRO               1128         71       1146       1147          
      1150 SPOUSE                1128         71                  1150              
      1152 SON                   1128         71       1128       1150          
      1149 FAT_BRO               1130         71       1146       1147          
      1150 SPOUSE                1130         71                                
      1152 SON                   1130         71       1130       1150          

I have another table named gender which has following data:

     ID   	SEX
    ------      ----
    1128	F
    1130	F
    1140        M 	
    1141	M
    1148	F
    1149	M
    1150	F
    1152	M
    1134        F
    1135        M
    1146        F
    1147        M

 

Now I want an output which has following format so that I can detect any insanity in the data:

ID   ID'SEX     PROBAND      PROBAND'SEX       MOTHER   MOTHER'SEX    FATHER   FATHER'SEX
---  ------     -------      -----------       ------   ----------    -------  ----------


If I use following join in SQL*PLUS: 
                         select relation.id, a.sex, proband, b.sex, mother, c.sex, father, d.sex
                         from relation, gender a, gender b, gender c, gender d
                         where relation.id=a.id and relation.proband=b.id and relation.mother=c.id
                         and relation.father=d.id

it only give me the result for the record whose mother and father fields are not null;

If I use outer join trying to take care of null field:

                         select relation.id, a.sex, proband, b.sex, mother, c.sex, father, d.sex
                         from relation, gender a, gender b, gender c, gender d
                         where relation.id=a.id and relation.proband=b.id and relation.mother(+)=c.id
                         and relation.father(+)=d.id

it give me a syntax error: You can't have 2 outer join at same time

If I just use one outer join, it still give me the same record like first one.

My question: How can we write one select statement to get the correct output ?

Thanks ahead!

yuhong Received on Fri Jan 14 1994 - 03:53:01 CET