Solved

Oracle query

Posted on 2013-02-07
11
283 Views
Last Modified: 2013-05-14
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!!!!
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864060
Something like this?

select b.*
from tableA a, tableB b
where a.empid=b.empid;
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 38864166
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
 
LVL 23

Expert Comment

by:paquicuba
ID: 38864178
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jknj72
ID: 38864210
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864258
>>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
 
LVL 23

Expert Comment

by:paquicuba
ID: 38864332
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
 

Author Comment

by:jknj72
ID: 38864400
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38864435
>>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
 
LVL 23

Accepted Solution

by:
paquicuba earned 500 total points
ID: 38864446
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
 

Author Closing Comment

by:jknj72
ID: 38865116
Very nice I think this will give me what Im looking for...Well done!!
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 38866210
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question