Re: 3 tables join

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

Relevant Pages

  • 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: 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)