Merging two tables

Hello

I am creating a new table by merging two tables. When I use outer join, it is creating dulplicates, if I have more number of records in either one of the tables.  

Example. I have two tables GRE and GMAT. Student1 (Key1) took GRE 3 times and GMAT once. Student2 (Key1) took GRE 2 times and GMAT once.  

SQL> select * from gre;

       Key1 GRE1 SUB1 SUB2
---------- ---------- ---------- ----------

         1          1        500        550
         1          2        550        580
         1          3        560        590
         2          1        550        560
         2          2        560        580

SQL> select * from gmat;

      Key1 GMAT1 SUB1 SUB2
---------- ---------- ---------- ----------

         1          1        570        580
         2          1        560        570

select gre.key1 "STUDENT",gre.gre1 "GRE1",gmat.gmat1 "GMAT1", gre.sub1 "GRE_SUB1", gre.sub2 "GRE_SUB2", gmat.sub1 "GMAT_SUB1",gmat.sub2 "GMAT_SUB2" from gre FULL OUTER JOIN gmat ON (gre.key1 = gmat.key1) order by gre.key1,gre1,gmat1

STUDENT GRE1 GMAT1 GRE_SUB1 GRE_SUB2 GMAT_SUB1 GMAT_SUB2 ---- ---- ----- -------- -------- --------- ---------

   1    1     1      500      550       570       580
   1    2     1      550      580       570       580
   1    3     1      560      590       570       580
   2    1     1      550      560       560       570
   2    2     1      560      580       560       570

Above outer join created duplicates in 2nd,3rd and 5th rows for GMAT_SUB1 and GMAT_SUB2.
For STUDENT 1, who took GRE three times (GRE: 1,2,3), there should be one matching GMAT record (GMAT: 1). GMAT should have the data for GRE = 1 and GMAT should be null for GRE 2,3.
The desired result is

KEY1 GRE1 GMAT1 GRE_SUB1 GRE_SUB2 GMAT_SUB1 GMAT_SUB2 ---- ---- ----- -------- -------- --------- ---------

   1    1     1      500      550       570       580
   1    2     1      550      580       NULL	  NULL
   1    3     1      560      590       NULL      NULL
   2    1     1      550      560       560       570
   2    2     1      560      580       NULL      NULL

Is there any way I can acheive the result set ?

Thank you for your help.

Krish Received on Mon Jun 17 2002 - 17:04:18 CDT