How to merge Subquery which returns multiple data for single row of table?

Question

My table contains startDate and endDate columns, so if I take its difference, it has multiple days in between, which I am able to do with one of my custom tables.

select btwnDates from CustomDateTable where btwnDates between '2019-01-5' and '2019-01-7';
2019-01-5
2019-01-6
2019-01-7

Now, I want to merge this output with other columns of the table.

My input table has value:

titles       description       startDate        endDate 
homelone     homeloan_policy   2018-06-07      2018-06-09  
carlone      carloan_policy    2019-07-18      2019-07-18

Output should be like:

titles       description     startDate        endDate          Day  
homelone   homeloan_policy   2018-06-07      2018-06-09     2018-06-07 
homelone   homeloan_policy   2018-06-07      2018-06-09     2018-06-08 
homelone   homeloan_policy   2018-06-07      2018-06-09     2018-06-09 
carlone    carloan_policy    2019-07-18      2019-07-18     2019-07-18

I found a json_extract_array_element_text function does the same but it works for json values also tried for arrays in redshift but no luck.

I am a newbie in redshift and its queries.

Answer 1

You seem to want a join:

select t.*, cd.btwnDates
from input t join
     customdatetable cd
     on cd.btwnDates >= t.startdate and
        cd.btwnDates >= t.enddate ;