How can I join two results

February 18, 2022

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:

>Solution :

I think you are looking for UNOIN ALL instead of OUTER FULL 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