CODEWITHSUNDEEP
mysqlsql
David
David

Reputation: 459

Joining different SQL tables based on dates/date range

I'm trying to figure out whether it's possible to join SQL tables on the basis of dates. So it's not as if and only if the dates will exactly coincide but I'm wondering if it's possible to link up tables based on whether the dates within one table fit within a range of dates in another. So it might look like this:

Table1.StartDate  Table1.EndDate   Table2Date
January 1, 2000   January 3, 2000, January 3, 2000
January 3, 2000   January 5, 2000, January 4, 2000

Is this possible?

Upvotes: 1

Views: 690

Answers (2)

Acewin
Acewin

Reputation: 1727

I do not see any constraint blocking any join condition. I quickly created and tested this in HSQL

CREATE TABLE PUBLIC.TBLX1 ( ID INTEGER NOT NULL, COUNTRY VARCHAR(25), CODE VARCHAR(25), CONTINET VARCHAR(25), CREATED DATE, UPDATED DATE, PRIMARY KEY (ID) );

CREATE TABLE PUBLIC.TBLX2 ( ID INTEGER NOT NULL, COUNTRY VARCHAR(25), CODE VARCHAR(25), CONTINET VARCHAR(25), CREATED DATE, UPDATED DATE, PRIMARY KEY (ID) );

INSERT INTO PUBLIC.TBLX2
(ID, COUNTRY, CODE, CONTINET, CREATED, UPDATED)
VALUES 
(1, 'USA', '102', 'North America', '2015-10-14', '2015-10-14');


INSERT INTO PUBLIC.TBLX1
(ID, COUNTRY, CODE, CONTINET, CREATED, UPDATED)
VALUES 
(1, 'USA', '102', 'North America', '2015-10-14', '2015-10-14');


select * from TBLX2
JOIN TBLX1 ON TBLX2.CREATED = TBLX1.CREATED

and it works out just fine.

Anything after ON needs to be a valid condition

Upvotes: 0

Siyual
Siyual

Reputation: 16917

Yes, you can JOIN any table to another based on whatever logic you want to use.

For your example, a query may look something like this:

Select  Table1.StartDate, Table1.EndDate, Table2.Date
From    Table1
Join    Table2  On  Table2.Date Between Table1.StartDate And Table1.EndDate

Upvotes: 1

Related Questions