Solved

Oracle query

Posted on 2013-02-07
11
282 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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 48

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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