Avatar of cookiejar
cookiejar
Flag 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'
Oracle Database

Avatar of undefined
Last Comment
cookiejar

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck