JOIN Multiple Table com base em timestamp e outra condição

Question

Quero juntar 3 tabelas cuja Tabela A tem data e hora em campo diferente que precisam ser combinadas primeiro para que possam ser comparadas na Tabela B usando o carimbo de data / hora do campo.

Tabela A

 userid |  date_in   | check_in
--------+------------+----------
 145    | 2017-01-23 | 08:56:05
 254    | 2017-01-24 | 08:56:54
 202    | 2017-01-25 | 08:53:26
 15     | 2017-01-26 | 08:47:40

Tabela B

 userid |      checktime      |      sn
--------+---------------------+---------------
 145    | 2017-01-23 08:56:05 | 0345135200184
 254    | 2017-01-24 08:56:54 | 0345135200184
 202    | 2017-01-25 08:53:26 | 0345135200184
 15     | 2017-01-26 08:47:40 | 0345135200184

Tabela 3

      sn       |    alias
---------------+-------------
 0345135200184 | Alam Sutera

Eu tentei isso:

select process.userid, process.date_in, process.check_in, checkinout.checktime, iclock.alias from process 
inner join checkinout on checkinout.checktime=(select cast (date_in as timestamp(0)) + (check_in - time '00:00:00') checktime from process)
inner join iclock on checkinout.sn=iclock.sn
order by userid desc limit 10;

Eu também tentei isso:

select checkinout.userid, checkinout.checktime, checkinout.sn from checkinout
inner join lateral
(select distinct userid, date_in, check_in from process where 
date_in=(select checktime::date from checkinout) and 
check_in=(select checktime::time from checkinout)
order by date_in asc limit 1)
process ON true;

Ambos fornecem o erro: ERROR: mais de uma linha retornada por uma subconsulta usada como uma expressão

Resultado esperado:

 userid |  date_in   | check_in |      checktime     | alias
--------+------------+----------+--------------------+-------
 145    | 2017-01-23 | 08:56:05 | 2017-01-21 09:49:04|Alam Sutera
 254    | 2017-01-24 | 08:56:54 | 2017-01-21 07:57:05|Alam Sutera
 202    | 2017-01-25 | 08:53:26 | 2017-01-21 14:27:00|Alam Sutera
 15     | 2017-01-26 | 08:47:40 | 2017-01-21 06:30:34|Alam Sutera

Alguém pode me ajudar a resolver isso? Obrigado pela ajuda.

Answer 1

Ao adicionar datee timecampo você obterá o timestamp que você pode comparar para participar. então você pode escrever sua consulta como abaixo:

select 
t1.userid, t1.date_in, t1.check_in, t2.checktime, t3.alias
from process t1
inner join checkinout t2 on t2. checktime= date_in + check_in and t1.userid=t2.userid
inner join table3 t3 on t2.sn=t3.sn

DEMO

O erro que você mencionou em sua pergunta ERROR: more than one row returned by a subquery used as an expressioné devido à condição de adesão que você usou.

Answer 2

Você pode converter suas colunas separadas de Data e Hora em um Timestamp usando a função To_Timestamp conforme mostrado abaixo. Isso deve permitir que você junte a Tabela A e a Tabela B conforme desejado.

select * from tableA a 
join tableB b 
on a.userid = b.userid 
and to_timestamp(a.date_in || ' ' || a.check_in, 'YYYY-MM-DD HH24:MI:SS') = b.checktime

Como demonstrado aqui