ceiling cat ceiling cat - 2 months ago 7
SQL Question

How do I join tables while preserving the exact information on one table?

I want to join two tables by matching a time in one table with a period (a start and end time) on the second, and I need to do this so that the operation preserves the exact information on one table. More specifically, I have these tables.

Table t1:

cid time1
A 2016-01-05 11:00:00
A 2016-01-15 11:00:00
A 2016-01-25 11:00:00
B 2016-01-09 11:00:00


Table t2:

cid period_start period_end
A 2016-01-01 00:00:00 2016-01-10 00:00:00
A 2016-01-10 00:00:00 2016-01-16 00:00:00
A 2016-01-12 00:00:00 2016-01-20 00:00:00


And I want the output as:

cid time1 period_start period_end
A 2016-01-05 11:00:00 2016-01-01 00:00:00 2016-01-10 00:00:00
A 2016-01-15 11:00:00 2016-01-10 00:00:00 2016-01-16 00:00:00
A 2016-01-25 11:00:00 NULL NULL
B 2016-01-09 11:00:00 NULL NULL


A few additional information/conditions:


  • I want the information on t1 preserved exactly in the output (e.g., no rows on t1 joined to multiple rows on t2, no rows from t1 missing in the output). In other words, I just want information from t2 added to t1 as columns.

  • If there is no period in t2 that includes time1 on t1, I want period_start and period_end to be NULL.

  • There might also be no matching cid on t2 at all.

  • If there are multiple matches on t2, I want only the first one.



Right now I have:

SELECT t1.*, t2.period_start, t2.period_end
FROM t1
LEFT JOIN t2 ON t1.cid = t2.cid
WHERE t2.period_start >= t1.time1
AND t2.period_end <= t1.time1


but it doesn't properly handle the scenario where there is no match. How can I do this?

I a doing this on Redshift.

Answer

Since you want only the first matching row from t2, you could use a LATERAL subquery with a LIMIT clause:

SELECT t1.cid, t1.time1, t2.period_start, t2.period_end
FROM t1 LEFT JOIN LATERAL
     (SELECT *
      FROM t2
      WHERE cid=t1.cid AND t1.time1 BETWEEN period_start AND period_end
      ORDER BY t2.period_start
      LIMIT 1
     ) t2 ON true