Solved

Oracle query

Posted on 2013-02-07
11
276 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
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Something like this?

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

Expert Comment

by:paquicuba
Comment Utility
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
Comment Utility
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
 

Author Comment

by:jknj72
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 23

Expert Comment

by:paquicuba
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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
Comment Utility
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
Comment Utility
Very nice I think this will give me what Im looking for...Well done!!
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

771 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now