Oracle query

Im trying to write a view that will return all the columns from one table and just the columns from another that dont exist in the first table...

Table A                           Table B
EMPID                             EMPID
LAST NAME                    LAST NAME
FIRST NAME                  FIRST NAME
                                           CITY
                                          STATE
                                          ZIP

Do I do a left join to table a with EMPID?
THANKS!!!!
jknj72Asked:
Who is Participating?
 
paquicubaCommented:
You can use listagg in 11gR2, but since I don't know your version, try this:


SELECT 'SELECT '||rtrim(xmlagg(XMLELEMENT(x,column_name||',') ORDER BY column_name).EXTRACT('//text()'),',')||
       ' FROM tablea A, tableb B WHERE A.empid = B.empid' sql_statement
FROM
(
SELECT 'A.'||column_name column_name
FROM dba_tab_columns WHERE table_name = 'TABLEA'
UNION ALL
SELECT 'B.'||column_name
FROM
(
SELECT column_name FROM dba_tab_columns WHERE owner = USER AND table_name = 'TABLEB'
MINUS
SELECT column_name FROM dba_tab_columns WHERE owner = USER AND table_name = 'TABLEA'
)
)
/
0
 
slightwv (䄆 Netminder) Commented:
Something like this?

select b.*
from tableA a, tableB b
where a.empid=b.empid;
0
 
paquicubaCommented:
You need to be more clear on what exactly you need.

If the above doesn't work, then try it like this:


SELECT
   A.last_name
  ,A.first_name
  ,b.city
  ,b.state
  ,b.zip
FROM tablea A, tableb b
WHERE A.empid = b.empid;
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
paquicubaCommented:
You can also try an outer join:

SELECT
   A.empid
   A.last_name
  ,A.first_name
  ,b.city
  ,b.state
  ,b.zip
FROM tablea A, tableb b
WHERE A.empid = b.empid(+);
0
 
jknj72Author Commented:
Is there anything I could write where I dont have to explicitly call each column and just give me everything from table A and only columns in Table B where it does NOT EXISTS in Table A? I want to design this so if I create a column in either table I dont have to put it in the query. There has to be a way!!! I just wish I knew it...
0
 
slightwv (䄆 Netminder) Commented:
>>only columns in Table B where it does NOT EXISTS in Table A?

Can you provide some sample data and expected results?

>>There has to be a way!!!

You 'can' write dynamic SQL and grab the individual columns from user_tab_columns but it would be a lot of work for potentially little gain.

Are you wanting to do this for some form of auditing/tracking?

If you can provide a little info on the task/problem you are trying to solve we  may be able to offer better advice.
0
 
paquicubaCommented:
I agree...dynamic SQL will be the only solution for your problem, but you may end up doing more work that way than modifying your static SQL every time you add or drop a column from either table.
0
 
jknj72Author Commented:
The sample data is above I just wanted to write something once and it pick up potential new columns in either table. The task is we have one table tblInfo that has existing data in it that was gathered through the years. Well now there is a master table(tblMaster)  that has about 3/4 of the columns of tblInfo. I want to combine the 2 tables with a Distinct number of columns that if the columns exist in both, then use the column from tblMaster and then if the columns only exists in tblInfo bring those columns into the view from tblInfo. FYI, I believe there will not be a column in tblMaster that doesnt exist in tblInfo!! If its gonna be too complicated then I can explicitly call the columns, I just figured there would be a relatively easy way to do it but I could easily be wrong?
Let me know your thoughts...
0
 
slightwv (䄆 Netminder) Commented:
>>The sample data is above

I see some table definitions.  I don't see actual sample data and expected results.

>>just figured there would be a relatively easy way to do it but I could easily be wrong?

I don't think so.  Especially since you mentioned a view.  There isn't a way to dynamically add a column to a view when it is added to a table. Well, I guess you 'could' write a DDL trigger that would dynamically re-create the view for you but have fund writing/testing that.
0
 
jknj72Author Commented:
Very nice I think this will give me what Im looking for...Well done!!
0
 
PortletPaulfreelancerCommented:
The question morphed...

"do I do left join?" became "how do I do a dynamic field list" and using the data dictionary is smart, well done paquicuba;

personally I would propose using ANSI join syntax

 ' FROM tablea A INNER JOIN tableb B ON A.empid = B.empid' sql_statement

but we are assuming an inner join is all you need (hopefully this is true)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.