Problem solve Get help with specific problems with your technologies, process and projects.

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
   v_colz   tab3.colz%type;
   select colz
     into v_colz
     from tab3
    where coly = p_col ;
   return v_colz;
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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.