Re: 3 tables join

bdbafh wrote:
On Nov 8, 4:16 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
bdbafh wrote:
On Nov 8, 10:46 am, Nick <nachiket.shirwal...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx (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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: select Statement w/i inner joins
    ... In my below example number 1 does not have any data in TableA or ... TableB therefor I do not want it in my results ... Number TableB Amount TableC Amount ... Please reply to the newsgroup. ...
    (microsoft.public.access.queries)
  • Re: SQL server DB design question
    ... > TableA. ... DB2 has TableB and TableC. ... > IDB ...
    (microsoft.public.sqlserver.server)
  • Re: 3 tables join
    ... from TableB and TableC depending on the key in TableA. ... TableB - columns{id, column1} ... the query should match the related key to the id of TableA or table B ...
    (comp.databases.oracle.server)
  • RE: Relational dependency checker for deleting a row
    ... create table tableBunique, colA int not ... create trigger trTableA_IO_Delete on tableA ... create trigger trTableB_IO_Delete on tableB ... insert into tableC values ...
    (microsoft.public.sqlserver.programming)
  • 3 tables join
    ... I have have 3 tables TableA, TableB and TableC. ... TableB - columns{id, column1} ...
    (comp.databases.oracle.server)