Solved

joining two tables and getting where records doesnt exist in second

Posted on 2013-01-16
16
471 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
  • 6
  • 4
  • 4
  • +1
16 Comments
 
LVL 12

Expert Comment

by:jmcmunn
Comment Utility
0
 

Author Comment

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

Author Comment

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

Accepted Solution

by:
Steve Wales earned 125 total points
Comment Utility
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
Comment Utility
Sjwales...Let me please give it a try and will let you know...shortly.

Thanks,
0
 
LVL 6

Expert Comment

by:Javier Morales
Comment Utility
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 6

Assisted Solution

by:Javier Morales
Javier Morales earned 125 total points
Comment Utility
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
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:schinni
Comment Utility
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 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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 22

Expert Comment

by:Steve Wales
Comment Utility
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
Comment Utility
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 22

Expert Comment

by:Steve Wales
Comment Utility
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 6

Expert Comment

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

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

Expert Comment

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

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

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ā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

743 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