Re: 3 tables join

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 08 Nov 2007 13:16:13 -0800
Message-ID: <1194556568.377269@bubbleator.drizzle.com>

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Thu Nov 08 2007 - 15:16:13 CST