I have 3 tables purchase, air_transfers and sea_transfers. I have made the example code in db fiddle. My problem is on 6th paragraph where I don’t have any sea transfers, I get null as id and thats because I’m using id from sea transfers. What should I do to fix this?
purchase table:
CREATE TABLE purchase ( id SERIAL PRIMARY KEY, date DATE NOT NULL )
air transfer table:
CREATE TABLE air_transfers ( id SERIAL PRIMARY KEY, purchase_id INTEGER NOT NULL REFERENCES purchase(id), units INTEGER NOT NULL )
sea transfers table:
CREATE TABLE sea_transfers ( id SERIAL PRIMARY KEY, purchase_id INTEGER NOT NULL REFERENCES purchase(id), units INTEGER NOT NULL )
my outer join:
WITH sea_transfers AS ( SELECT purchase_id, SUM(units) AS units FROM sea_transfers GROUP BY purchase_id ), air_transfers AS ( SELECT purchase_id, SUM(units) AS units FROM air_transfers GROUP BY purchase_id ) SELECT st.purchase_id, (COALESCE(st.units,0) + COALESCE(at.units,0)) AS units FROM sea_transfers AS st FULL OUTER JOIN air_transfers AS at ON st.purchase_id = at.purchase_id
if my sea transfers is empty I get this result:
Advertisement
Answer
I think you are looking for UNION ALL
instead of FULL OUTER JOIN
, because UNION ALL
will combine two results air_transfers
and sea_transfers
tables, you will get all purchase_id
and units
values from two tables.
SELECT purchase_id, SUM(units) AS units FROM ( SELECT purchase_id,units FROM air_transfers UNION ALL SELECT purchase_id,units FROM sea_transfers ) t1 GROUP BY purchase_id