CODEWITHSUNDEEP
mysqljoin
Anujith
Anujith

Reputation: 9370

Get the latest data by datetime fields from two tables

This question is slightly different from my previous question : Get the latest data comparing datetime fields from two table

I have two tables with identical structure like this:

Table : user

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |      john123 |  2014-06-04 14:58:22 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |     marie123 |  2014-06-04 15:00:06 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
|  8 | Bruce  |     bruce123 |  2014-06-04 15:07:11 |
+----+--------+--------------+----------------------+

Table : user_k

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |       abu123 |  2014-06-04 14:53:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |  shane123NEW |  2014-06-04 15:00:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |      mike123 |  2014-06-04 15:00:36 |
|  6 | kiron  |     kiron123 |  2014-06-04 15:02:18 |
|  7 | Dan    |       dan123 |  2014-06-04 15:09:46 |
+----+--------+--------------+----------------------+

Now I need to fetch the latest data (by 'LastUpdateTime') from these two tables, like this:

+----+--------+--------------+----------------------+
| ID | Name   |    Password  |    LastUpdateTime    |
+----+--------+--------------+----------------------+
|  1 | abu    |    abu123NEW |  2014-06-04 14:55:06 |
|  2 | john   |   john123NEW |  2014-06-04 14:59:48 |
|  3 | shane  |     shane123 |  2014-06-04 15:02:06 |
|  4 | marie  |  marie123NEW |  2014-06-04 15:03:17 |
|  5 | mike   |   mike123NEW |  2014-06-04 15:01:32 |
|  6 | kiron  |  kiron123NEW |  2014-06-04 15:05:46 |
|  7 | Dan    |       dan123 |  2014-06-04 15:09:46 |
|  8 | Bruce  |     bruce123 |  2014-06-04 15:07:11 |
+----+--------+--------------+----------------------+

What I have now:

select
u.id,
u.name,
if(u.LastUpdateTime >= k.LastUpdateTime, u.password, k.password) as password,
greatest(u.LastUpdateTime, k.LastUpdateTime) as LastUpdateTime
from
user u
inner join user_k k on u.id = k.id

But this fetches only those records that are available with both tables... I need the data that is present in any one table of the tables also. Also, I'm concerned about the query performance with large set of data.

Here is the sqlfiddle

PS: Besides name & password there are a dozen more fields(columns) in both tables. I avoid them just to simplify the question.

Upvotes: 0

Views: 106

Answers (2)

Anujith
Anujith

Reputation: 9370

I have one solution:

SELECT * from 

(SELECT u.*
FROM user u LEFT OUTER JOIN user_k uk ON u.ID = uk.ID WHERE 
(uk.ID IS NULL OR u.LastUpdateTime > uk.LastUpdateTime)

UNION ALL

SELECT uk.*
FROM user_k uk LEFT OUTER JOIN user u ON u.ID = uk.ID WHERE 
(u.ID IS NULL OR uk.LastUpdateTime >= u.LastUpdateTime)) usr

order by usr.ID

SQL FIDDLE

Upvotes: 0

Strawberry
Strawberry

Reputation: 33935

SELECT x.*
  FROM 
     ( SELECT * FROM user
       UNION ALL
       SELECT * FROM user_k
     ) x
  JOIN 
     ( SELECT id,MAX(lastupdatetime) max_lut
         FROM
            ( SELECT * FROM user
               UNION ALL
              SELECT * FROM user_k
            ) a
        GROUP
           BY id
     ) y
    ON y.id = x.id
   AND y.max_lut = x.lastupdatetime
 ORDER
    BY id;

http://sqlfiddle.com/#!2/05d8be/14

Upvotes: 2

Related Questions