Q
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
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
```

#### Start the conversation

Send me notifications when other members comment.

## SearchDataManagement

• ### Lessons learned from Credit Karma GraphQL architecture

Credit Karma's vice president of engineering explains why and how the personal finance service is using the GraphQL data query ...

• ### EnterpriseDB looks to grow market for PostgreSQL

Enterprises are increasingly using the open source PostgreSQL database. Read in this Q&A where the growth is coming from and how ...

• ### 3 of the top use cases for graph databases

Graph databases establish many unique relationships between data points. These unusual relationships are beneficial in many use ...

• ### Embedded BI software creates common ground for diverse analytics

Learn how embedding separate business intelligence capabilities into one application empowers users to drill down, access and ...

• ### Embedded BI and analytics apps speed workflows, insight access

Embedded BI is fast becoming a focal point for analytics uses as data analysts, developers and business users enjoy faster ...

• ### What Salesforce means for Tableau in the cloud

After the Salesforce acquisition, users have wondered about the future of Tableau in the cloud. Experts weigh in on how the ...

## SearchSAP

• ### Understand SAP ECC vs. HANA vs. S/4HANA vs. R/3

SAP ECC, HANA, S/4HANA and R/3 are all valuable tools in the IT world, but it's easy to get them confused. Learn about their ...

• ### 5 tips for a successful S/4HANA Cloud implementation

Moving to S/4HANA public cloud can help you save on IT maintenance and simplify real-time analysis. Here's a look at what you can...

• ### Take care of data before SAP S/4HANA migration

In this Q&A, Rajesh Rengarethinam of ERP security vendor Appsian discusses why reviewing data security and business processes are...

## SearchSQLServer

• ### SQL Server database design best practices and tips for DBAs

Good database design is a must to meet processing needs in SQL Server systems. In a webinar, consultant Koen Verbeeck offered ...

• ### SQL Server in Azure database choices and what they offer users

SQL Server databases can be moved to the Azure cloud in several different ways. Here's what you'll get from each of the options ...

• ### Using a LEFT OUTER JOIN vs. RIGHT OUTER JOIN in SQL

In this book excerpt, you'll learn LEFT OUTER JOIN vs. RIGHT OUTER JOIN techniques and find various examples for creating SQL ...

## TheServerSide.com

• ### CSS performance optimization in 5 easy steps

Here are five CSS performance tips to help you avoid website performance problems.

• ### Why the 8 Java primitive data types are not objects

What's the difference between Java primitive types and objects? To start, don't classify primitive types as objects. Let's ...

• ### A brief history of Java: How it forever changed programming

The Java programming language is an important tool in an application developer's quiver. But Java has also undergone many changes...

## SearchDataCenter

• ### Plan for a successful ITIL implementation

Corporate buy-in and training are essential for organizations that decide to adopt the ITIL framework. Admins should also help ...

• ### Learn the main Linux OS components

Linux is one operating choice to run on your infrastructure. Get started with these terms to discover how the OS works and how it...

• ### New Dell EMC PowerEdge servers are built for the rugged edge

New Dell Streaming Media Platform includes two small form factor PowerEdge servers and a Modular Data Center. Dell aims the ...

## SearchContentManagement

With new Google Drive integration, Adobe embeds free features for commenting and annotating PDFs inside Google Docs. Acrobat ...

• ### 4 benefits of business process automation

Companies are automating business processes to improve workflows and use technology effectively. Some benefits of BPA include ...

• ### Test yourself on the differences between SharePoint and OneDrive

Businesses use Microsoft SharePoint and OneDrive capabilities for a number of reasons. Test your knowledge on the differences ...

## SearchHRSoftware

• ### Trump's H-1B work visa actions disappoint ex-Disney workers

President Donald Trump arrived in office promising to reform the H-1B visa program. But his actions have fallen short of what ...

• ### How real-time analytics can benefit HR

For analytics to be truly useful, it must be embedded into user workflows. Learn how HR tools are incorporating such approaches ...

• ### Human vs. AI in recruiting: Why both matter

Recruiters may consider using both the human element and the data brought in by intelligent tools if it can help them make better...

Close