Querying data from to nonrelated tables in SQL-CodePudding

I have a question in terms of SQL. Let's make up a simplified example with two tables:

Table A contains a period's name, its start date an its end date

Table B contains dates

What I want to do is to make a query, where I use Table B's date and in another column I want the Period to be shown. Logically, I would use a condition like

Table B.date <= Table A.enddate AND Table B.date >= Table A.startdate

to get the period's name, the date in question belongs to. However, as I cannot retate these two tables as table in a relational database are normally, I am currently stuck with this issue.

CodePudding user response：

In relational database you typically see "equi-joins" where you relate tables using equality predicates.

However, you are not limited to these simple predicates. You can use any predicate you want to relate the tables. These are usually called "theta joins".

In your case you can do:

select a.*, b.*
from table_a a
join table_b b on b.date >= a.startdate and b.date <= a.enddate

Sample data:

create table table_a (name varchar(10), startdate date, enddate date);

insert into table_a (name, startdate, enddate) values
  ('Q1', '2021-01-01', '2021-03-31'),
  ('Q2', '2021-04-01', '2021-06-30'),
  ('Q3', '2021-07-01', '2021-09-30'),
  ('Q4', '2021-10-01', '2021-12-31');
  
create table table_b (date date);

insert into table_b (date) values ('2021-05-21'), ('2021-09-07');

Result:

 name  startdate   enddate     date      
 ----- ----------  ----------  ----------
 Q2    2021-04-01  2021-06-30  2021-05-21
 Q3    2021-07-01  2021-09-30  2021-09-07

See running example at DB Fiddle.