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....).
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 ;
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
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in November 2002