Link to home
Start Free TrialLog in
Avatar of cookiejar
cookiejarFlag for United States of America

asked on

left join and where clause oracle 11g

I would like to construct only on query from the following four queries.  I would like return all of the results of query 1 but only  return results from queries 2 through 4 where column_name and table_name match query 1 column_name and table name.

1. select table_name, COLUMN_NAME, DATA_TYPE, DATA_LENGTH FROM DBA_TAB_COLUMNS
WHERE TABLE_NAME IN ('EMPLOYEE','DEPARTMENTS', 'PAYROLL', 'HUMAN RESOURCE')

-- Get primary key
2. SELECT cols.table_name, cols.column_name, 'Yes' primary_key
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
and cons.owner = 'TEST'

-- Get foreign key  
3. SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 'Yes' foreign key
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
       AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R' and a.owner = 'TEST'
 
--Nullable
4. SELECT TABLE_NAME,COLUMN_NAME,NULLABLE 'No' Null FROM ALL_TAB_COLUMNS WHERE OWNER = 'TEST'
ASKER CERTIFIED SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of cookiejar

ASKER

Correct syntax :  

3.         SELECT a.table_name, a.column_name, a.constraint_name, c.owner, 'Yes' foreign_key,
       -- referenced pk
       c.r_owner, c_pk.table_name r_table_name, c_pk.constraint_name r_pk
  FROM all_cons_columns a
  JOIN all_constraints c ON a.owner = c.owner
       AND a.constraint_name = c.constraint_name
  JOIN all_constraints c_pk ON c.r_owner = c_pk.owner
                           AND c.r_constraint_name = c_pk.constraint_name
 WHERE c.constraint_type = 'R' and a.owner = 'TEST'
   
  4.     SELECT TABLE_NAME,COLUMN_NAME,NULLABLE  FROM ALL_TAB_COLUMNS WHERE OWNER = ''TEST'
       and nullable = 'N'