CODEWITHSUNDEEP
sqlpostgresqlleft-joincross-joingenerate-series
Jon
Jon

Reputation: 4055

Combine 2 tables to join to

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

Upvotes: 0

Views: 148

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657912

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():

Upvotes: 1

Jon
Jon

Reputation: 4055

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

Upvotes: 0

NewK
NewK

Reputation: 353

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

Upvotes: 0

Related Questions