CODEWITHSUNDEEP
sqlpostgresqljoin
jlrosenberg
jlrosenberg

Reputation: 287

Left Outer Join with 2 columns

I'm having an issue with a postrgresql join. I might be approaching it incorrectly, but here's the scenario.

I have a table which contains two relevant columns: dates and months (along with other data). Each date should have the next 5 months associated with it, inclusive. This isn't always the case; I want to find when this isn't the case. Additionally, there is no guarantee that each date is in the table (for which there should be 5 months), but I have another table which contains these dates.

The table should contain (for one date):

Expected

However, due to many possibilities the table may only contain:

Possible

I have attempted to find the missing dates by generating a series for the expected dates and joining a series of months that should be associated with the date. I'm running into an issue because I need to join the tables on the two columns I need, so if one doesn't exist, it doesn't make it through the ON or WHERE clause.

I might need to approach this differently, but here is my current attempt.

SELECT 
    D.date, JOINMONTH::date, DT.month
FROM
    day D
CROSS JOIN
    generate_series(date_trunc('month', D.date),
                    date_trunc('month', D.date) + INTERVAL '4 months',
                    '1 month') AS JOINMONTH
LEFT JOIN 
    dates_table DT ON D.date = DT.date 
                   AND JOINMONTH::date = DT.month
WHERE
    D.date >= '2018-01-01';

What I would like to see:

Result (goal)

EDIT: This db-fiddle gives my full query. I omitted some of the where clause because I thought it was irrelevant, but it seems to be part of the problem. With this in mind, my selected answer will solve my problem represented by this structure/query but @Gordon Linoff's answer is correct for the original question.

Upvotes: 0

Views: 743

Answers (2)

jlrosenberg
jlrosenberg

Reputation: 287

SELECT D.date, JOINMONTH::date, DT.month
FROM day D 
CROSS JOIN LATERAL
     generate_series(date_trunc('month', D.date),
                     date_trunc('month', D.date) + INTERVAL '4 months',
                     '1 month') AS JOINMONTH 
LEFT JOIN dates_table DT
    ON D.date = DT.date 
    AND JOINMONTH::date = DT.month
    AND DT.source = 'S1' AND
    DT.tf = TRUE
WHERE 
    D.date = '2018-11-02';

I needed to move parts of my where clause into the join itself.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Is this what you are looking for?

SELECT D.date, JOINMONTH::date, DT.month
FROM day D CROSS JOIN LATERAL
     generate_series(date_trunc('month', D.date),
                     date_trunc('month', D.date) + INTERVAL '4 months',
                     '1 month') AS JOINMONTH LEFT JOIN
     dates_table DT
     ON GD.date = DT.date AND JOINMONTH::date = DT.month
WHERE D.date >= '2018-01-01' AND DT.date IS NULL;

Upvotes: 1

Related Questions