Re: Three-Way table join

parris_at_walleye.esp.bellcore.com (Parris Geiser) wrote:

>Hi,
> I would like to produce a report from the tables:
> user_tab_columns
> all_constraints
> user_cons_columns
> that looks like the following:
>TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH CONSTRAINT_NAME CONSTRAINT_TYPE
 
>Does anyone know how to do a join to produce this?
> Thanks,
> parris

Try this:

  SELECT a.table_name, a.column_name, a.data_type, a.data_length,

                 b.constraint_name, b.constraint_type
     FROM user_tab_columns a,
                all_constraints b,
                all_cons_columns c
  WHERE a.table_name = c.table_name
        AND a.column_name = c.column_name
        AND c.constraint_name = b.constraint_name
        AND a.table_name = 'PUT TABLE NAME HERE'

You can probably rework the join a little to get better efficiency depending on what table you want to be the driving table.

Tim

timp_at_asymetrix.com Received on Wed Jan 17 1996 - 00:00:00 CET