Re: 3 tables join

On Nov 8, 4:16 pm, DA Morgan <damor..._at_psoug.org> wrote:
> bdbafh wrote:
> > 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
> > /
>
> You realize that if their instructor sees this you just cost them
> a chance of passing. At UW this would be grounds for dismissal or
> other disciplinary actions.
>
> You do no one a favor by providing complete solutions other than
> hints except if you think there is value in helping people cheat.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Daniel,

There was enough lag in the post showing up here that it appears that I posted after your post.
I didn't.
This was posted before you asked for no answers on this thread.

-bdbafh Received on Thu Nov 08 2007 - 15:58:10 CST