Re: 3 tables join

From: bdbafh <bdbafh_at_gmail.com>
Date: 8 Nov 2007 12:13:36 -0800
Message-ID: <1194544810.311608.299450@q5g2000prf.googlegroups.com>

On Nov 8, 10:46 am, Nick <nachiket.shirwal..._at_gmail.com> wrote:
> I have have 3 tables TableA, TableB and TableC. TableA holds the keys
> to TableB and TableC. I need a query which will display the details
> from TableB and TableC depending on the key in TableA.
>
> For eg.
>
> TableA - columns {id, relatedkey, recordType} ===recordType will hold
> values like TableB or TableC
> TableB - columns{id, column1}
> TableC - columns{id, column1}
>
> the query should match the related key to the id of TableA or table B
> based on recordType and show the column1 value with the TabelA id so
> output for this should be
>
> id recordType column1
>
> 1 TableB value of TableB column1
> 2 TableC value of TableC column1
>
> Please help.
>
> Cheers
> Nick

ok. lets assume the following:

TableA	all_objects
TableB	all_tables
TableC	all_sequences

so here rows in TableB correspond to entries in TableA but not to rows in TableC.
This isn't exactly your question, but it does assist you in gaining familiarity with the data dictionary.
It may be a bit heavy on resource consumption ... add filters as you deem appropriate. rownum<11 added just to provide only 10 rows.

-bdbafh

select * from (
select o.owner, o.object_name, o.object_type, t.table_name   from all_objects o, all_tables t

 where o.owner = t.owner
   and o.object_name = t.table_name
   and o.object_type='TABLE'

union all
select o.owner, o.object_name, o.object_type, s.sequence_name   from all_objects o, all_sequences s

 where o.owner = s.sequence_owner
   and o.object_name = s.sequence_name
   and o.object_type='SEQUENCE'

)
where rownum<11
/ Received on Thu Nov 08 2007 - 14:13:36 CST