Re: Merging two tables

You would need to outer join on two columns. I'm not really comfortable with the ANSI SQL ... so I'll do it the other way:  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, gmat
where gre.key1 = gmat.key1(+)
and gre.gre1 = gmat.gmat1(+) <-------------Add this  order by gre.key1,gre1,gmat1

Hope it helps,

Anurag

"Krishna" <krishnandsu_at_netscape.net> wrote in message news:7bda40b6.0206171404.4316412f_at_posting.google.com...
> 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 Tue Jun 18 2002 - 19:42:30 CDT