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. stackoverflow.com/questions/11391085/… — Keith John Hutchison
– Keith John Hutchison, Commented Jan 19, 2017 at 6:38
From google search every date from start to finish postgresql google.com.au/… — Keith John Hutchison
– Keith John Hutchison, Commented Jan 19, 2017 at 6:39

Giorgos Betsos · Accepted Answer · 2017-01-19 06:43:55Z

8

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

answered Jan 19, 2017 at 6:43

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: How to join two tables using between date?

TABLE_1

TABLE_2

RESULT:

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

TABLE_1

TABLE_2

RESULT:

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related