JOIN with tables without relationship

Question

I have a report that contains many JOINS and the base of the report is a table called “table_class” (c). What I need is to compare a Date Time field of this table (table_class) with another table called “table_period”. The problem is that the table “table_period” has no relationship with the “table_class”. I need to show the PK of the “table_period” when the Start Date field (Date Time) of “table_class” is between the Start Date and End Date (both Date Time) from “table_period”.

Basically, I want to show the period by class according the period that the class occurs/occurred.

Important: the “table_period” is flexible at user level, because the period does not have fixed dates.

For example:
1st semester 2016: from Jan 01, 2016 to Jun 30, 2016
2nd semester 2016: from Jul 01, 2016 to Dec 31, 2016
1st semester 2017: from Feb 01, 2017 to Jul 31, 2017
2nd semester 2017: from Aug 01, 2017 to Jan 31, 2018

And go on…So, I can’t fix the period.

How can we do this? Is it possible to create a JOIN with this scenario or even solving with a CASE expression?

To clarify better, we can see an example of those tables bellow.

Table 1: table_class (c) (reminding that this table is the base of the report)

CLASS_ID | START_DTE             | END_DTE
0001     | Jun 29, 2017, 8:00 AM | Jun 29, 2017, 3:30 PM
0002     | Jan 11, 2018, 8:00 AM | Jan 12, 2018, 3:30 PM
...

Table 2: table_period (p)

PERIOD_ID         | START_DTE            | END_DTE
1st semester 2016 | Jan 1, 2016, 3:00 AM | Jun 30, 2016, 1:00 AM
2nd semester 2016 | Jul 1, 2016, 1:00 AM | Dec 31, 2016, 3:00 AM
1st semester 2017 | Feb 1, 2017, 3:00 AM | Jul 31, 2017, 1:00 AM
2nd semester 2017 | Aug 1, 2017, 1:00 AM | Jan 31, 2018, 3:00 AM
...

What I expect

c.CLASS_ID | p.PERIOD_ID       | c.START_DTE           | c.END_DTE
0001       | 1st semester 2017 | Jun 29, 2017, 8:00 AM | Jun 29, 2017, 3:30 PM
0002       | 2nd semester 2017 | Jan 11, 2018, 8:00 AM | Jan 12, 2018, 3:30 PM
...

Thank you!

Have you tried simple join [table_class c join table_period p on c.start_date between p.start_date and p.end_date] ? — Maxim Borunov
@MaximBorunov, I've not tried this before! But worked as expected! Tks! — Igor Ivanov
Join does not need a "relationship" (FK). Every join is meaningful. Inner join does a cross join & keeps only the rows meeting the on condition. Or you could just cross join & use where. Just write the condition you want. — philipxy

Gordon Linoff Gordon Linoff · Accepted Answer · 2018-06-19T17:58:09

You can do a join like this:

select c.*, p.period_id
from table_class c left join
     table_period p
     on c.start_dte < p.end_dte and c.start_dte >= p.start_dte;

This assigns the period -- if any -- based on when the class starts.

JOIN with tables without relationship

1 Answers