Re: 3 tables join

From: DA Morgan <damorgan_at_psoug.org>
Date: Thu, 08 Nov 2007 14:47:54 -0800
Message-ID: <1194562069.847395@bubbleator.drizzle.com>

bdbafh wrote:

> 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

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
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 - 16:47:54 CST