Ask the Expert

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!


    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: