Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2002
    Posts
    2

    Unanswered: Merging two tables

    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 is creating duplicates.GRE1 and GMAT1 columns have different data,
    so they cannot be joined.
    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

    STUDENT 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

  2. #2
    Join Date
    Apr 2002
    Posts
    6
    Hi,

    You can try this query. I am not sure if you are using Oracle or any other database. I tried this query with Oracle and it works for your current requirement.

    But if you want the result for the reverse case i.e. appearing gmat more times than gre then it may not work. But you can start thinking on this angle.

    SQL> select gre.key1 "STUDENT",
    gre.gre1 "GRE1",
    gmat.gmat1 "GMAT1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gre.sub1,gre.sub1) "GRE_SUB1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gre.sub2,gre.sub2) "GRE_SUB2",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gmat.sub1,null) "GMAT_SUB1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gmat.sub2,null) "GMAT_SUB2"
    from gre, gmat
    where gre.key1 = gmat.key1 (+)
    order by gre.key1,gre1,gmat1
    SQL> /

    STUDENT GRE1 GMAT1 GRE_SUB1 GRE_SUB2 GMAT_SUB1 GMAT_SUB2
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    1 1 1 500 550 570 580
    1 2 1 550 580
    1 3 1 560 590
    2 1 1 550 560 560 570
    2 2 1 560 580

  3. #3
    Join Date
    Jun 2002
    Posts
    2
    Thank you for your suggestion. It would work, if know that GRE has more records. But we don't know which table has more records than the other.

    Thanks
    Krish

    Originally posted by debjit
    Hi,

    You can try this query. I am not sure if you are using Oracle or any other database. I tried this query with Oracle and it works for your current requirement.

    But if you want the result for the reverse case i.e. appearing gmat more times than gre then it may not work. But you can start thinking on this angle.

    SQL> select gre.key1 "STUDENT",
    gre.gre1 "GRE1",
    gmat.gmat1 "GMAT1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gre.sub1,gre.sub1) "GRE_SUB1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gre.sub2,gre.sub2) "GRE_SUB2",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gmat.sub1,null) "GMAT_SUB1",
    decode(gre.key1||gre.gre1,
    gmat.key1||gmat.gmat1,
    gmat.sub2,null) "GMAT_SUB2"
    from gre, gmat
    where gre.key1 = gmat.key1 (+)
    order by gre.key1,gre1,gmat1
    SQL> /

    STUDENT GRE1 GMAT1 GRE_SUB1 GRE_SUB2 GMAT_SUB1 GMAT_SUB2
    ---------- ---------- ---------- ---------- ---------- ---------- ----------
    1 1 1 500 550 570 580
    1 2 1 550 580
    1 3 1 560 590
    2 1 1 550 560 560 570
    2 2 1 560 580

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •