Re: 3 tables join

Giganews Newsgroups
Subject: Re: 3 tables join
Posted by:  bdbafh (bdba…
Date: 8 Nov 2007

On Nov 8, 10:46 am, Nick <nachiket.shirwal.…> 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.


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


In response to

3 tables join posted by Nick on Thu, 08 Nov 2007