Combine 2 tables to join to

Question

I'm using Postgres 9.1 and I have a query which returns 2 columns which I would like replicated even if no data is present. The important parts of my query are

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id

This returns to me all institutions and all interval_times just like I'd expect. Now I also want rows where there is no data. I can do the following to get either one at a time

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = interval_time

or

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id
ON all_institution_id = institution_id

however, I'd like to combine them, so that I get all interval_times for all institutions. It seems like the best thing to do is to create some kind of intermediate table which contains each institution_id repeated for each interval_time and then RIGHT JOIN on that, but I'm not sure how to do that. Any help would be much appreciated.

EDIT

After playing around a little bit, it seems like what I want do to is the following, but my syntax is wrong

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id,
generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = dose_hsts.interval_time
AND all_institution_id = institution_id

So do you have your answer? – Erwin Brandstetter Sep 03 '19 at 21:21 — Erwin Brandstetter, Sep 03 '19 at 21:21

Erwin Brandstetter · Answer 1 · 2019-09-03T21:21:52.773

I think you want a CROSS JOIN before you do the LEFT JOIN (replacing the RIGHT JOIN to the same effect here):

SELECT interval_time::date , institution_id, ...
FROM   generate_series(date_trunc('week', timestamp '2012-02-03')
                     , timestamp '2013-02-18'
                     , interval '1 week')   AS d(interval_time)
CROSS  JOIN unnest(array[1, 3, 4, 5, 7, 9]) AS i(institution_id)
LEFT   JOIN (
   SELECT date_trunc('week', tx_dttm) AS interval_time, institution_id
   FROM   ...
   GROUP  BY 1, 2
   ) x USING (interval_time, institution_id);

About generate_series():

Generating time series between two dates in PostgreSQL

score 0 · Answer 2 · answered Mar 27 '13 at 16:40

I think what you want is UNION both queries. Try this:

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
UNION
SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN generate_series('2012-02-03', '2013-02-18', interval '1 week')
ON DATE_TRUNC('week', generate_series) = interval_time

score 0 · Answer 3 · answered Mar 27 '13 at 16:40

I figured it out by using the following syntax

SELECT DATE_TRUNC('week', tx_dttm) AS interval_time, institution_id FROM (...)
WHERE institution_id IN (1, 3, 4, 5, 7, 9) GROUP BY interval_time, institution_id
RIGHT JOIN (SELECT * FROM unnest(array[1, 3, 4, 5, 7, 9]) AS all_institution_id,
generate_series('2012-02-03', '2013-02-18', interval '1 week'))
ON DATE_TRUNC('week', generate_series) = dose_hsts.interval_time
AND all_institution_id = institution_id

Combine 2 tables to join to

3 Answers3