We help IT Professionals succeed at work.
Get Started

PL/SQL - Combine Results of Several Select Queries

Glenn Stearns
on
1,252 Views
Last Modified: 2013-12-07
I have three SELECT queries with each one returning a result set such as:
Query 1: emp_id    amt  from table1.fieldname
Query 2: emp_id    amt from table2.fieldname
Query 3: emp_id    amt from table3.fieldname

The emp_id field is also in each of the three tables, but the way the SELECT statements are written, the result set of each query includes the same list of emp_id's.

I would like to combine the three queries into one so that I get a result set that looks like this:

emp_id     amt table1.fieldname   amt table2.fieldname   amt table3.fieldname  

If this is possible, then it is my preferred approach, but I don't know how to combine the output of multiple SELECT queries into a single result set.  If not, then here are the others I've considered but don't know how to do:

I don't want to ALTER TABLE on any of the three tables to add a field for amt to them. If it has to be that I need to create a temporary table in order to get this to work, then I'd need to know the syntax to add the table (which I can then update with the result of each query accordingly) and then, after I've used the data, I'd want to remove the temporary table.  I could also create a new table with the emp_id field and a field for each of the three amounts, then update that table with update statements included in each of the three queries, then update it again when I'm done, returning the fields to NULL.  If this seems the most logical, I'd prefer to do it that way than to alter an existing table or create a temporary one, since I will be running this sequence once each week.

What's the best approach, and for that best approach, how would I write the SQL to do it?
Comment
Watch Question
Production Manager / Application Support Manager
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 21 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE