I need to left join two tables with a where condition:
time_table
id rid start_date end_date
1 2 2017-07-01 00:00:00 2018-11-01 00:00:00
2 5 2017-01-01 00:00:00 2017-06-01 00:00:00
3 2 2018-07-01 00:00:00 2020-11-01 00:00:00
record_table
id name date
1 record1 2017-10-01 00:00:00
2 record2 2017-02-01 00:00:00
3 record3 2017-10-01 00:00:00
I need to get all those records which are present under given date range. In the above example, I need those records that lie under range for rid = 2
only. Hence the output for the above query needs to be:
1 record1 2017-10-01 00:00:00
3 record3 2017-10-01 00:00:00
There is a trap waiting for you here. When using a
LEFT [OUTER] JOIN
, it's typically wrong to filter with aWHERE
condition, thereby voiding the special feature of aLEFT JOIN
to include all rows from the left table unconditionally. Detailed explanation:So, put conditions into the
WHERE
clause that are supposed to filter all rows (rid = 2
), but make conditions to left-join rows fromrecord_table
out to be actual join conditions:As commented, it makes sense to include columns from
time_table
in the result, but that's my optional addition.You also need to be very clear about upper and lower bounds. The general convention is to include lower and exclude the upper bound in time (
timestamp
) ranges. Hence my use of>=
and<
above.Related:
Performance should be no problem at all with the right indexes. You need an index (or PK) on
time_table
on(rid)
and another onrecord_table
on(date)
.I'm not exactly sure if this is what you want, but if you are saying you want the dates where the record_table date is between the dates in the time_table, then this would do the job:
That said, this will be horribly inefficient for large datasets. If your data is relatively small (< 10k records in each table, post-filters), then it probably won't matter much, but if you would need to scale this concept, it would warrant knowing more about your data -- for example, do the dates, always round to the first of each month?
Again, from your example, I wasn't sure if this is what you meant by "get all those records which are present under given date range."