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?