Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: viewing multiple columns

    I want to view table name, column name, and data type for the columns named STREET, CITY, STATE, ZIP. Here is my script whats wrong?

    select dba_tables.table_name, column_name, data_type
    from dba_tables, dba_tab_columns
    where column_name='STREET' and column_name='CITY' and column_name='STATE' and column_name='ZIP'
    order by column_name, table_name;

    Thanks,
    Sam

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    select t.table_name, t.tablespace_name, c.column_name, c.data_type
    from dba_tables t
    INNER JOIN
    dba_tab_columns c ON
    t.table_name = c.table_name
    where t.table_name IN
    (select table_name
    from dba_tab_columns
    where column_name IN ('STREET', 'CITY', 'STATE', 'ZIP')
    group by table_name
    having count(column_name) = 4);
    Last edited by r123456; 03-01-04 at 06:02.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    ss_luna, you didn't join dba_tables and dba_tab_columns tables. And, I'd say you don't need dba_tables table at all to achieve what you've asked for.
    Besides that, you can NOT use such WHERE clause as it won't return anything - you'd rather use IN than "column_name = value".

    r123456, I tried it too. My query would be
    PHP Code:
    select c.table_namec.column_namec.data_type
    from dba_tab_columns c
    where c
    .column_name in ('STREET''CITY''STATE''ZIP')
    order by c.table_namec.column_name
    This query seems to be less complicated than yours. Why did you use INNER JOIN? Actually, I wouldn't ask if your query returned anything. But it didn't ... Now, I don't know why. (I runned both of them on Oracle 9.)
    Last edited by Littlefoot; 03-01-04 at 03:25.

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    TableA
    Street, City, State, Zip

    TableB
    Street, City, State

    The query that you supplied would return both TableA and TableB, however the solution I provided will only return TableA.

    The inner join provides the capability to retrieve additional information.
    Last edited by r123456; 03-01-04 at 06:01.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    OK, r123456, but look at this (I modified column names to fit those in my database, to be able to collect some data as an output):

    Your query:
    PHP Code:
    select t.table_namet.tablespace_namec.column_namec.data_type
    from dba_tables t
    INNER JOIN
    dba_tab_columns c ON
    t
    .table_name c.table_name
    where t
    .table_name IN
    (select table_name
    from dba_tab_columns
    where column_name IN 
    ('OMM_ID''POOL_ID''OCI_ID''RB')
    group by table_name
    having count
    (column_name) = 4);

    0 rows selected 
    My query:
    PHP Code:
    select c.table_namec.column_namec.data_type
    from dba_tab_columns c
    where c
    .column_name in ('OMM_ID''POOL_ID''OCI_ID''RB')
    order by c.table_namec.column_name;

    TABLE_NAME                     COLUMN_NAME                    DATA_TYPE         
    ------------------------------ ------------------------------ ------------------
    GOD_POTR_BCK                   OMM_ID                         NUMBER            
    POOL_KONTR_LISTA               OMM_ID                         NUMBER            
    POOL_KONTR_LISTA               POOL_ID                        NUMBER            
    STAVKE_AKO                     OMM_ID                         NUMBER            
    ...
             
    39 rows selected 
    So, the query you provided (in my database) returns nothing; one would hope for Table A's data.

    And I still don't understand why INNER JOIN in this case? Does your query really returns something when you run it?

  6. #6
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The solution I provided, will return tables that contain only the complete set of columns specified.

    Code:
    SQL> desc tablea;
     Name                   
     ---------------------
     STREET                 
     CITY                   
     STATE                  
     ZIP                    
    
    SQL> desc tableb;
     Name                   
     ---------------------
     STREET                 
     CITY                   
     STATE                  
    
    SQL> select t.table_name, t.tablespace_name, c.column_name, c.data_type
      2  from user_tables t
      3  INNER JOIN
      4  user_tab_columns c ON
      5  t.table_name = c.table_name
      6  where t.table_name IN
      7  (select table_name
      8  from user_tab_columns
      9  where column_name IN ('STREET', 'CITY', 'STATE', 'ZIP') AND
     10  table_name IN ('TABLEA', 'TABLEB')
     11  group by table_name
     12  having count(column_name) = 4);
    
    TABLE_NAME                     TABLESPACE_NAME                COLUMN_NAME                    DATA_TYPE
    ------------------------------ ------------------------------ ------------------------------ -------
    TABLEA                         SYSTEM                         STREET                         NUMBER
    TABLEA                         SYSTEM                         CITY                           NUMBER
    TABLEA                         SYSTEM                         STATE                          NUMBER
    TABLEA                         SYSTEM                         ZIP                            NUMBER
    
    SQL> select c.table_name, c.column_name, c.data_type
      2  from user_tab_columns c
      3  where c.column_name in ('STREET', 'CITY', 'STATE', 'ZIP') AND
      4  c.table_name IN ('TABLEA', 'TABLEB')
      5  order by c.table_name, c.column_name;
    
    TABLE_NAME                     COLUMN_NAME                    DATA_TYPE
    ------------------------------ ------------------------------ ---------
    TABLEA                         CITY                           NUMBER
    TABLEA                         STATE                          NUMBER
    TABLEA                         STREET                         NUMBER
    TABLEA                         ZIP                            NUMBER
    TABLEB                         CITY                           NUMBER
    TABLEB                         STATE                          NUMBER
    TABLEB                         STREET                         NUMBER
    Last edited by r123456; 03-01-04 at 21:23.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Now! I understand what you wanted to say in the first place! Thank you for explaining it to me, r123456. Sorry for being such a pain in the _ _ _

    I hope there's still Summer in Brisbane (as parts of my country suffer under 4 meters (12 feet) of snow).

    Looking forward to learn something new from you soon,

    Littlefoot

Posting Permissions

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