Re: Merging two tables

Krishna wrote:

> 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

If records are being duplicated you have not written a sufficiently selective WHERE clause. The join being an outer join is likely not the issue. Rather it is the data and what additional join conditions may need to be specified.

Daniel Morgan Received on Tue Jun 18 2002 - 00:24:59 CDT