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