CODEWITHSUNDEEP
sqldatejoin
Stiffo
Stiffo

Reputation: 818

SQL join two tables on earliest date only

I'm not great at SQL and having trouble understanding the other answers.

My request is similar to these two: Join on earliest date and select earliest date.

I have the following two tables:

T1

user   |  lock_time   
----------------------
a | 2016-11-03 04:53:57
b | 2016-11-14 00:50:38
b | 2016-12-04 20:52:18
c | 2016-11-14 16:36:29
d | 2016-11-14 16:41:01
d | 2016-11-15 11:22:29

T2

user   |  unlock_time   
----------------------
a | 2016-11-04 05:47:51
b | 2016-11-15 13:56:28
b | 2016-12-05 23:10:51
d | 2016-11-15 13:36:29

I want to join these table, in such a way that each lock_time is paired to the only the first unlock_time, which is greater than the lock_time being joined. So the resulting example table would be:

user   |  lock_time   | unlock_time
-------------------------------------
a | 2016-11-03 04:53:57 | 2016-11-04 05:47:51
b | 2016-11-14 00:50:38 | 2016-11-15 13:56:28
b | 2016-12-04 20:52:18 | 2016-12-05 23:10:51
d | 2016-11-14 16:41:01 | 2016-11-15 13:36:29

I am here interested in the time between lock_time and unlock_time, so for instances where there's no possible matches between lock/unlock, that row should be dropped.

It is guaranteed there will not be multiple lock_time or unlock_time on a give day, but there could for instance exist two lock_time with different dates, that only match to one unlock_time. In this case I want the first lock_time.

Example: user A locked on 2016-11-03 and 2016-11-04, but only unlocked on 2016-11-04.

Upvotes: 1

Views: 5514

Answers (3)

Vlad Bochenin
Vlad Bochenin

Reputation: 3072

You can join tables by condition l.user = u.user and l.lock_time < u.unlock_time and get minimal unlock_time

select l.user, l.lock_time, min(u.unlock_time) as unlock_time
from
   T1 l
   JOIN T2 u on l.user = u.user and l.lock_time < u.unlock_time
group by l.user, l.lock_time;

UPDATE: For case with d

T1

user   |  lock_time   
----------------------
d      | 2016-11-14 16:41:01
d      | 2016-11-15 11:22:29

T2

user   |  unlock_time   
----------------------
d      | 2016-11-15 13:36:29

and result:

user   |  lock_time          | unlock_time
-------------------------------------
d      | 2016-11-14 16:41:01 | 2016-11-15 13:36:29

you can get minimal lock_time after you've got minimal unlock_time

select l1.user, min(l1.lock_time) lock_time, l1.unlock_time
from (
     SELECT
        l.user,
        l.lock_time,
        min(u.unlock_time) AS unlock_time
     FROM
         T1 l
         JOIN T2 u ON l.user = u.user AND l.lock_time < u.unlock_time
     GROUP BY l.user, l.lock_time
) as l1
group by l1.user, l1.unlock_time

Upvotes: 3

Suman Behara
Suman Behara

Reputation: 160

You have not posted full table columns details, I wrote query based on your example. It may work try it.

select t1.user,t1.lock_time, t2.unlock_time  from T1 t1, T2 t2 where t1.user=t2.user and t2.unlock_time >= t1.lock_time;

Upvotes: -1

jarlh
jarlh

Reputation: 44706

Simple way - use a correlated sub-query to find the user's first unlock_time after lock_time:

select t1.user, t1.lock_time,
      (select min(t2.unlock_time) from t2
       where t2.unlock_time > t1.lock_time
         and t1.user = t2.user)
from t1

Upvotes: 3

Related Questions