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!

Data from tables having uncommon Columns

Discussion in 'SQL PL/SQL' started by robin, Sep 15, 2011.

  1. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Hi,

    I have two tables :

    emp(id,name,sal);

    dept(name,loc);

    I want to retrieve data from emp and dept .I want to retrieve only those columns that are not common to emp and dept i.e I would like to retrieve columns : id,sal and loc.Name is common and so I donot wish to retrieve name.

    I have no idea how to do this.I selected column_names from all_tab_columns

    Code (SQL):
    SELECT column_name FROM all_tab_columns WHERE TABLE_NAME='emp';

    SELECT column_name FROM all_tab_columns WHERE TABLE_NAME='dept';

    any thoughts on this ?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You need to consider a table join between the two; since NAME is common between them that column should be used as your join condition. Do you understand joins? Do you know how to construct a query using a join?
     
  3. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Yes I know how to use a sql join but I am not interested in any condition between the columns.I simply wish to display the other columns that are not common.In this case I wish to display all the columns except for name column.How can I retrieve other columns if I use join on name ? I donot want name column.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,346
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    The column name may be common but the column data is NOT. The name column in emp refers to the employee name while the name column in dept refers to the dept name; since you have no joinable columns what you want will return

    1) far MORE data than you have in either table
    2) useless rows having nothing at all to do with each other

    For example:

    Code (SQL):
     
    SQL>
    SQL> --
    SQL> -- Create example tables
    SQL> --
    SQL>
    SQL> CREATE TABLE emp(id NUMBER, name varchar2(40), sal NUMBER);
    TABLE created.
    SQL>
    SQL> CREATE TABLE dept(name varchar2(40), loc varchar2(40));
    TABLE created.
    SQL>
    SQL> --
    SQL> -- Load data
    SQL> --
    SQL>
    SQL> BEGIN
      2          FOR i IN 1..20 loop
      3                  INSERT INTO emp
      4                  VALUES(i, to_char(to_date(i,'j'), 'Jsp'), 5000*i);
      5          END loop;
      6  
      7          commit;
      8  
      9  END;
     10  /
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> INSERT ALL
      2  INTO dept
      3  VALUES ('Marketing','Boston')
      4  INTO dept
      5  VALUES ('Sales','Juneau')
      6  INTO dept
      7  VALUES('Accounting','Duluth')
      8  INTO dept
      9  VALUES('Research','Antlers')
     10  SELECT * FROM dual;
    4 ROWS created.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> --
    SQL> -- Return data from both tables minus
    SQL> -- the 'name' columns
    SQL> --
    SQL> -- Returns useless noise
    SQL> --
    SQL>
    SQL> SELECT id, sal, loc
      2  FROM emp, dept;
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
             1       5000 Boston
             2      10000 Boston
             3      15000 Boston
             4      20000 Boston
             5      25000 Boston
             6      30000 Boston
             7      35000 Boston
             8      40000 Boston
             9      45000 Boston
            10      50000 Boston
            11      55000 Boston
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
            12      60000 Boston
            13      65000 Boston
            14      70000 Boston
            15      75000 Boston
            16      80000 Boston
            17      85000 Boston
            18      90000 Boston
            19      95000 Boston
            20     100000 Boston
             1       5000 Juneau
             2      10000 Juneau
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
             3      15000 Juneau
             4      20000 Juneau
             5      25000 Juneau
             6      30000 Juneau
             7      35000 Juneau
             8      40000 Juneau
             9      45000 Juneau
            10      50000 Juneau
            11      55000 Juneau
            12      60000 Juneau
            13      65000 Juneau
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
            14      70000 Juneau
            15      75000 Juneau
            16      80000 Juneau
            17      85000 Juneau
            18      90000 Juneau
            19      95000 Juneau
            20     100000 Juneau
             1       5000 Duluth
             2      10000 Duluth
             3      15000 Duluth
             4      20000 Duluth
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
             5      25000 Duluth
             6      30000 Duluth
             7      35000 Duluth
             8      40000 Duluth
             9      45000 Duluth
            10      50000 Duluth
            11      55000 Duluth
            12      60000 Duluth
            13      65000 Duluth
            14      70000 Duluth
            15      75000 Duluth
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
            16      80000 Duluth
            17      85000 Duluth
            18      90000 Duluth
            19      95000 Duluth
            20     100000 Duluth
             1       5000 Antlers
             2      10000 Antlers
             3      15000 Antlers
             4      20000 Antlers
             5      25000 Antlers
             6      30000 Antlers
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
             7      35000 Antlers
             8      40000 Antlers
             9      45000 Antlers
            10      50000 Antlers
            11      55000 Antlers
            12      60000 Antlers
            13      65000 Antlers
            14      70000 Antlers
            15      75000 Antlers
            16      80000 Antlers
            17      85000 Antlers
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
            18      90000 Antlers
            19      95000 Antlers
            20     100000 Antlers
    80 ROWS selected.
    SQL>
     
    Notice for the 20 rows in emp you get 80 rows of, well, nonsense as the query returns 20*4 rows all due to no valid join condition.

    Changing the example slightly:

    Code (SQL):

    SQL> --
    SQL> -- Change emp table
    SQL> --
    SQL>
    SQL> ALTER TABLE emp ADD d_name varchar2(40);
    TABLE altered.
    SQL>
    SQL> --
    SQL> -- Assign departments to employees
    SQL> --
    SQL>
    SQL> UPDATE emp
      2  SET d_name = 'Accounting'
      3  WHERE MOD(id,3) = 0;
    6 ROWS updated.
    SQL>
    SQL> UPDATE emp
      2  SET d_name = 'Sales'
      3  WHERE d_name IS NOT NULL
      4  AND MOD(id,5) = 0;
    1 ROW updated.
    SQL>
    SQL> UPDATE emp
      2  SET d_name = 'Marketing'
      3  WHERE d_name IS NULL;
    14 ROWS updated.
    SQL>
    SQL> commit;
    Commit complete.
    SQL>
    SQL> --
    SQL> -- New query
    SQL> --
    SQL> -- Returns correct and useful values
    SQL> --
    SQL>
    SQL> SELECT id, sal, loc
      2  FROM emp, dept
      3  WHERE emp.d_name = dept.name;
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
             1       5000 Boston
             2      10000 Boston
             3      15000 Duluth
             4      20000 Boston
             5      25000 Boston
             6      30000 Duluth
             7      35000 Boston
             8      40000 Boston
             9      45000 Duluth
            10      50000 Boston
            11      55000 Boston
            ID        SAL LOC
    ---------- ---------- ----------------------------------------
            12      60000 Duluth
            13      65000 Boston
            14      70000 Boston
            15      75000 Juneau
            16      80000 Boston
            17      85000 Boston
            18      90000 Duluth
            19      95000 Boston
            20     100000 Boston
    20 ROWS selected.
    SQL>
     
    Yes, those rows are buried in the orignial output but you have no way of knowing which are correct and which aren't. Unless the name column in the dept table refers to the employee name you have no way of returning what you want and knowing that the results aren't suspect.
     
  5. robin

    robin Forum Advisor

    Messages:
    55
    Likes Received:
    7
    Trophy Points:
    160
    Location:
    Hyderabad
    Thanks for your reply Zargaon.The question was just out of curiosity.