?
Solved

joining two tables and getting where records doesnt exist in second

Posted on 2013-01-16
16
Medium Priority
?
484 Views
Last Modified: 2013-01-24
Hello Friends..
We have two tables tab1 and tab2 same structures and empid and id are keys.

We need to query for records that doesnt exist in tab2 and exist in tab1 based on keys above and then insert into t2.
And also we have a date column in tab1...
if we have two records..that match empid and id and if dates are different we have to take one record with the least date like min(Date) and insert that one record in to tab2.

any help on this would be appreciated...

Thanks,
Chinn...
0
Comment
Question by:schinni
[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
  • 6
  • 4
  • 4
  • +1
16 Comments
 

Author Comment

by:schinni
ID: 38785747
No..That doesnt work..
If table 1 has two records with two different dates that doesnt exist in Tab 2 i want the record with the least date meaning min(date)
Thanks
0
 
LVL 12

Expert Comment

by:jmcmunn
ID: 38785816
Then you would want to first limit your result set from table 1 doing something like this:

insert into #tmptable
(select * from table1
group by employeeid,id
order by min(somedate))

Then proceed as if #tmpTable were table 1
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:schinni
ID: 38785824
I am not worried about insert...i want some how to join tables and get all the records that doesnt exist in table2 if there are dups i want the one which has the least date..

Appreciate your help!
0
 
LVL 23

Accepted Solution

by:
Steve Wales earned 375 total points
ID: 38785915
If you're just after a query, this seems to work:

(two tables, with 2 columns, called id and last - representing the last date of whatever it is we are tracking)

select a.id, case when (a.last < b.last and b.last is not null) then a.last else b.last end
from tab01 a
left outer join tab02 b
on a.id = b.id
order by a.id

Open in new window


Records that don't exist in tab2 will appear as null in column 2
0
 

Author Comment

by:schinni
ID: 38786877
Sjwales...Let me please give it a try and will let you know...shortly.

Thanks,
0
 
LVL 7

Expert Comment

by:Javier Morales
ID: 38787053
Well, I build that test sample.

SQL>  create table tab1 (id number, coldate date);

Table created.

SQL> create table tab2 (empid number, coldate date);    

Table created.

SQL> insert into tab1 values (1,sysdate);

1 row created.

SQL> insert into tab1 values (2,sysdate-1);

1 row created.

SQL> insert into tab2 values (2,sysdate-10);

1 row created.

SQL> insert into tab2 values (3,sysdate);

1 row created.

SQL> commit;

Commit complete.

Open in new window


Then,
tab1 has rows 1 and 2
tab2 has rows 2 (date older than row 2 in tab 1), and row 3

you want to insert in tab2 row 1 (missing in tab2) and check row 2 to update it if date is older.

SQL> select * from tab1;

        ID COLDATE
---------- ---------
         1 17-JAN-13
         2 16-JAN-13

SQL> select * from tab2;

     EMPID COLDATE
---------- ---------
         2 07-JAN-13
         3 17-JAN-13

Open in new window


Well, what you want to do is a conditional MERGE.

SQL> merge into tab2 using tab1 on (tab1.id=tab2.empid)
  2  when matched then update set tab2.coldate=tab1.coldate WHERE tab2.coldate>tab1.coldate
  3  when not matched then insert (tab2.empid,tab2.coldate) values (tab1.id,tab1.coldate);

1 row merged.

SQL> select * from tab2;    

     EMPID COLDATE
---------- ---------
         2 07-JAN-13
         3 17-JAN-13
         1 17-JAN-13

Open in new window


Hope it helps !
Kind regards,
Javier
0
 
LVL 7

Assisted Solution

by:Javier Morales
Javier Morales earned 375 total points
ID: 38787067
Here's the same test sample, including also a new row 5 with a date older than tab2 (has to be updated). And it works.

SQL> insert into tab1 values (5,sysdate-100);

1 row created.

SQL> insert into tab2 values (5,sysdate-50);

1 row created.

SQL> select * from tab1;

        ID COLDATE
---------- ---------
         1 17-JAN-13
         2 16-JAN-13
         5 09-OCT-12

SQL> select * from tab2;

     EMPID COLDATE
---------- ---------
         2 07-JAN-13
         3 17-JAN-13
         5 28-NOV-12

SQL> merge into tab2 using tab1 on (tab1.id=tab2.empid)
  2  when matched then update set tab2.coldate=tab1.coldate WHERE tab2.coldate>tab1.coldate
  3  when not matched then insert (tab2.empid,tab2.coldate) values (tab1.id,tab1.coldate);

2 rows merged.

SQL> select * from tab2;

     EMPID COLDATE
---------- ---------
         2 07-JAN-13
         3 17-JAN-13
         5 09-OCT-12
         1 17-JAN-13

Open in new window


kind regards,
Javier
0
 

Author Comment

by:schinni
ID: 38787621
Javier,
Thanks For the help!
I just want to do a insert if the record doesn exist.
From the below

SQL> select * from tab1;

        ID COLDATE
---------- ---------
         1 17-JAN-13
         2 16-JAN-13
         2 14-JAN-13
         6  16-JAN-13
         5 09-OCT-12

SQL> select * from tab2;

     EMPID COLDATE
---------- ---------
         3 17-JAN-13
         5 09-OCT-12

I want to insert below  records into tab2

2 14-JAN-13
6  16-JAN-13
1 17-JAN-13

Thanks....
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38787687
You seem to have 2 parts to your question.

First you wanted a list of everything and if there were duplicates, then report the earlier of the two dates.

There have been several examples in the previous answers.

For the insert of missing rows, doesn't this do what you need done ?

insert into tab02
(select * from tab01 where tab01.id not in (select id from tab02));

Open in new window

0
 

Author Comment

by:schinni
ID: 38787721
Sjwales,
Kind of two parts but want in one query..
find missing rows if there any get them but if there are two records(duplicate) get the one with least date.

hope i am not confusing...

Thanks,
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38787769
I am sorry, but I am confused.

Of these two parts, one is "insert missing rows" - which really doesn't produce any output other than "x row(s) inserted".

The other part is a query showing all the rows, handling duplicate dates with returning the earliest.

(If a row is missing, it can't be duplicated because it's missing).

I'm afraid I can't think of a way to have one statement that does both a report on data at the same time it does an insert.

Can you elaborate any further on the requirement ?
0
 

Author Comment

by:schinni
ID: 38787813
Sorry...I have tables and data like below...

SQL> select * from tab1;

     empid    ID COLDATE
----------       ---------
   12           1 17-JAN-13
   10          2 16-JAN-13
   10          2 14-JAN-13
   16           6  16-JAN-13
   17            5 09-OCT-12

SQL> select * from tab2;

  EMPID    ID COLDATE
---------- -    --------
   21          3 17-JAN-13
   17           5 09-OCT-12

I want to write a query to the get the date as below....

10          2 14-JAN-13 --because this has least date among 2 in tab1
16           6  16-JAN-13
12           1 17-JAN-13

Thanks Again...
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 38787901
OK try this (but it still doesn't address the insert part):

select a.id, min(case when (a.last < b.last and b.last is not null) then a.last else b.last end)
from tab01 a
left outer join tab02 b
on a.id = b.id
group by a.id
order by a.id

Open in new window

0
 
LVL 7

Expert Comment

by:Javier Morales
ID: 38787925
You can use a query instead of tab1 in MERGE statement.

Sorry, I'm using phone :/
0
 
LVL 7

Expert Comment

by:Javier Morales
ID: 38787975
Well, I'm back to laptop.

Something like this?

SQL> insert into tab1 values (1,sysdate-5);

1 fila creada.

SQL> select * from tab1;

        ID COLDATE
---------- --------
         1 17/01/13
         2 16/01/13
         1 12/01/13

SQL> select * from tab2;

     EMPID COLDATE
---------- --------
         2 07/01/13
         3 17/01/13

Open in new window


So, row 1 does not exists in tab2, and it's duplicated. You want the minor date to be inserted.

SQL> merge into tab2
  2    using (select id, min(coldate) coldate from tab1 group by id) tab1
  3    on (tab1.id=tab2.empid)
  4  when matched then update set tab2.coldate=tab1.coldate WHERE tab2.coldate>tab1.coldate
  5  when not matched then insert (tab2.empid,tab2.coldate) values (tab1.id,tab1.coldate);

1 fila fusionada.

SQL> select * from tab2;

     EMPID COLDATE
---------- --------
         2 07/01/13
         3 17/01/13
         1 12/01/13

Open in new window

0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

719 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