1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

create table with join

Discussion in 'SQL PL/SQL' started by Oracle_, Sep 23, 2012.

  1. Oracle_

    Oracle_ Guest

    I have two tables:

    table_a:

    table_a (field1 varchar2(10),extra_field varchar2(6))

    field1 extra_field
    '9282013055' '123'
    '9345015055' '345'
    '9345015055' '678'

    table_b:

    table_b (field2 varchar2(10), field3 varchar2(10), field4 varchar2(10), field5 varchar2(10))

    field2 field3 field4 field5
    '9282013055' '120/340' '4434' 'David'
    '9645015111' '120/340' '4434' 'Sami'
    '9745015999' '120/340' '4434' 'Julia'
    '9345015055' '140/440' '4424' 'Mary'

    Note: field3 and field4 in table_b define the rows that correspond to field1 in table_a.

    I need to create a third table, table_c, where the required output should be:

    table_c:

    field2 field5 extra_field
    '9282013055' 'David' '123'
    '9645015111' 'Sami' '123'
    '9745015999' 'Julia' '123'
    '9345015055' 'Mary' '345'
    '9345015055' 'Mary' '678'

    I am trying this statement:

    create table_c as select field2,field5 from table_b where
    (field3,field4) in (select field3,field4 from table_b where field2 in (select
    field1 from table_a)

    which gets the rows corresponding to field1 in table_a (by using field3 and field4 in table_b).

    but I need to get extra_field from table_a as well. I am guessing a join should be made between table_a and table_b.

    Any help appreciated.



    Any help appreciated.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It won't be a simple join:

    Code (SQL):
    SQL> CREATE TABLE table_a
      2  (field1 varchar2(10),extra_field varchar2(6));
     
    TABLE created.
     
    SQL>
    SQL> CREATE TABLE table_b
      2  (field2 varchar2(10), field3 varchar2(10), field4 varchar2(10), field5 varchar2(10));
     
    TABLE created.
     
    SQL>
    SQL> INSERT ALL
      2  INTO table_a
      3  VALUES('9282013055','123')
      4  INTO table_a
      5  VALUES('9345015055','345')
      6  INTO table_a
      7  VALUES('9345015055','678')
      8  INTO table_b
      9  VALUES('9282013055','120/340','4434','David')
     10  INTO table_b
     11  VALUES('9645015111','120/340','4434','Sami')
     12  INTO table_b
     13  VALUES('9745015999','120/340','4434','Julia')
     14  INTO table_b
     15  VALUES('9345015055','140/440','4424','Mary')
     16  SELECT * FROM dual;
     
    7 ROWS created.
     
    SQL>
    SQL> commit;
     
    Commit complete.
     
    SQL>
    SQL> CREATE TABLE table_c
      2  AS SELECT
      3  field2, field5, extra_field
      4  FROM table_a a JOIN table_b b ON (b.field2 = a.field1)
      5  UNION
      6  SELECT field2, field5, (SELECT MIN(extra_field) FROM table_a)
      7  FROM table_b
      8  WHERE field2 NOT IN (SELECT field1 FROM table_a);
     
    TABLE created.
     
    SQL>
    SQL> SELECT *
      2  FROM table_c;
     
    FIELD2     FIELD5     EXTRA_
    ---------- ---------- ------
    9282013055 David      123
    9345015055 Mary       345
    9345015055 Mary       678
    9645015111 Sami       123
    9745015999 Julia      123
     
    SQL>
    Not every record is matched between table_a and table_b.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Instead , FULL OUTER JOIN could also be used for the same as shown below.

    Code (SQL):
    CREATE TABLE table_c
     AS
    SELECT
        field2, field5,
        NVL(  extra_field ,  (SELECT   MIN(extra_field) FROM table_a) ) EXTRA
    FROM table_a a FULL OUTER JOIN table_b b ON (b.field2 = a.field1);
     
  4. kimipatel

    kimipatel Active Member

    Messages:
    53
    Likes Received:
    1
    Trophy Points:
    140
    Full outer joint is use for this. I have also tried by using above command n it's works.

    create table tab_c AS

    select field2, field5,
    NVL( extra_field , (select min(extra_field) from tab_a) ) extra
    from tab_a a FULL OUTER JOIN tab_b b ON (b.field2 = a.field1);

    Try this way.