Re: Merging two tables

Thank you guys for your responses. I can not join gre.gre1 and gmat.gmat1, since they have different values (This is an example, I have tables that have different values). Either one of the tables can have more records than the other. I guess, I need to write complex PL/SQL or PRO*C program.

Thanks
Krish

"Anurag" <avdbi_at_hotmail.com> wrote in message news:<ugvkrs9nnbr632_at_corp.supernews.com>...
> 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 Wed Jun 19 2002 - 11:16:25 CDT