Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: 3 tables join

Re: 3 tables join

From: DA Morgan <>
Date: Thu, 08 Nov 2007 14:47:54 -0800
Message-ID: <>

bdbafh wrote:

> On Nov 8, 4:16 pm, DA Morgan <> wrote:
>> bdbafh wrote:
>>> On Nov 8, 10:46 am, Nick <> 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
>> (replace x with u to respond)
>> Puget Sound Oracle Users
> 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

Thanks I appreciate that this happens sometimes.

Note to everyone ... and yes I am a bit sensitive given what I do at the university ... students need a helping hand ... not a cheat sheet.

No one anywhere is using SCOTT/TIGER or SH, OR or HR as a production schema. When you see basic level questions with basic level table names, emp, dept, TableA, etc. these are students. Please help them ... don't cripple them.

Again thanks bdbafh.

Daniel A. Morgan
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Thu Nov 08 2007 - 16:47:54 CST

Original text of this message