troubleshooting Question

left join and where clause oracle 11g

Avatar of cookiejar
cookiejarFlag for United States of America asked on
Oracle Database
2 Comments1 Solution570 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros