sqlpostgresqlpg

How can I join two results


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:

enter image description here


Solution

  • 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