PostgreSQL: How to join two tables using between date?

Question

I really don't know how to ask this question of mine.

I'll illustrate it using two tables I needed to join.

TABLE_1

Name    Date
John    01-01-2016
May     04-08-2015
Rose    10-25-2016
Mary    12-15-2015
Ruby    07-07-2017

TABLE_2

Signatory       DateFrom    DateTo
President 1     01-01-2015  12-31-2015
President 2     01-01-2016  12-31-2016

RESULT:

Name    Date        Signatory
John    01-01-2016  President 2
May     04-08-2015  President 1
Rose    10-25-2016  President 2
Mary    12-15-2015  President 1
Ruby    07-07-2017  NULL

All I need to check if the Date of Table_1 is within the DateFrom and DateTo of Table_2 to get the Signatory field.

How I can do that?

Thanks a lot! ^_^

Essentially you need a table of every date. Checkout the answers here. http://stackoverflow.com/questions/11391085/getting-date-list-in-a-range-in-postgresql — Keith John Hutchison, Jan 19 '17 at 06:38
From google search every date from start to finish postgresql https://www.google.com.au/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=every+date+from+start+to+finish+postgresql — Keith John Hutchison, Jan 19 '17 at 06:39

score 8 · Accepted Answer · answered Jan 19 '17 at 06:43

Try this:

SELECT t1.*, t2.Signatory
FROM Table_1 AS t1
LEFT JOIN Table_2 AS t2 
   ON t1."Date" BETWEEN t2.DateFrom AND t2.DateTo

What you need is just a LEFT JOIN with BETWEEN in the ON clause in order to determine whether Date field of Table_1 falls within any [DateFrom, DateTo] interval of Table_2.

Demo here

PostgreSQL: How to join two tables using between date?

TABLE_1

TABLE_2

RESULT:

1 Answers1