Solved

Oracle query

Posted on 2013-02-07
11
280 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)
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
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.

 

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 76

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 76

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

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.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
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 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

860 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