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

i want join 2 tables matching time in 1 table period (a start , end time) on second, , need operation preserves exact information on 1 table. more specifically, have these tables.

table t1:

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

table t2:

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

and want output as:

cid   time1                 period_start          period_end     2016-01-05 11:00:00   2016-01-01 00:00:00   2016-01-10 00:00:00     2016-01-15 11:00:00   2016-01-10 00:00:00   2016-01-16 00:00:00     2016-01-25 11:00:00   null                  null b     2016-01-09 11:00:00   null                  null 

a few additional information/conditions:

• i want information on t1 preserved in output (e.g., no rows on t1 joined multiple rows on t2, no rows t1 missing in output). in other words, want information t2 added t1 columns.
• if there no period in t2 includes time1 on t1, want period_start , period_end null.
• there might no matching cid on t2 @ all.
• if there multiple matches on t2, want first one.

right have:

select t1.*, t2.period_start, t2.period_end t1   left join t2 on t1.cid = t2.cid t2.period_start >= t1.time1 , t2.period_end <= t1.time1 

but doesn't handle scenario there no match. how can this?

i doing on redshift.

since want first matching row t2, use lateral subquery limit clause:

select t1.cid, t1.time1, t2.period_start, t2.period_end t1 left join lateral      (select *       t2       cid=t1.cid , t1.time1 between period_start , period_end       order t2.period_start       limit 1      ) t2 on true