Q

Creating a view from a 3-table join

I've been tearing my hair out trying to figure out this problem, and have finally decided to bow to the omnipotent gurus on Ask The Experts. I am attempting to create a view from a 3-table join. Using Oracle 8.1.7.

Instead of wasting your time trying to explain my problem in English, I've created an example of the three tables and the resulting view I'm trying to create as an HTML page at http://www.storm.ca/~dodger/join_question.html.

I initially would have thought this would be fairly simple, but the number of hours I've poured into this with zero results are beginning to take their toll on my sanity. If you know of a simple solution which actually works, I'll be forever in your debt!


I don't remotely claim omnipotence or "guru" status, but here's my seven-minute answer (I actually timed how long it took me)! I've given you two options so depending on how much data you have in the tables, you'll need to decide which method which would perform better (use explain plan, autotrace, sql_trace and the like....).

Method 1:

Create a function as follows:

create or replace function get_colz (p_col in number)
return varchar2
as
   v_colz   tab3.colz%type;
begin
   select colz
     into v_colz
     from tab3
    where coly = p_col ;
   return v_colz;
end;
/
This function will be used to retrieve the text value from table 3.

Then here's the select statement for your view:

create or replace view stop_the_insanity as
select col1, 
       get_colz(colb) as col2, 
       get_colz(colc) as col3, 
       get_colz(cold) as col4, 
       get_colz(cole) as col5, 
       get_colz(colf) as col6
  from tab1, tab2
 where tab1.col2 = tab2.cola ;
 

Method 2:

create or replace view stop_the_insanity_2 as
select col1, t2.colz, t3.colz, t4.colz, t5.colz, t6.colz
  from tab1, tab2, tab3 t2, tab3 t3, tab3 t4, tab3 t5, tab3 t6
 where tab1.col2 = tab2.cola
   and tab2.colb = t2.coly
   and tab2.colc = t3.coly
   and tab2.cold = t4.coly
   and tab2.cole = t5.coly
   and tab2.colf = t6.coly ;

Either method results in:

 C COL2       COL3       COL4       COL5       COL6
 - ---------- ---------- ---------- ---------- ---------
 A Hello      Thanks     Stop       Stop       Goodbye
 B Goodbye    Hello      Thanks     Goodbye    Goodbye
 C Stop       Hello      Go         Thanks     Goodbye
 D Go         Go         Hello      Stop       Thanks

For More Information


This was first published in November 2002

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close