Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE PO (
    Vendor VARCHAR(100),
    PONum INT PRIMARY KEY,
    POAmt NUMERIC(10, 2)
);

CREATE TABLE Reference1 (
    PONum INT PRIMARY KEY,
    RefNum INT
);

CREATE TABLE Reference2 (
    PONum INT PRIMARY KEY,
    RefNum INT
);

CREATE TABLE Invoice (
    RefNum INT PRIMARY KEY,
    InvNum VARCHAR(10),
    InvAmt NUMERIC(10, 2)
);

INSERT INTO PO (Vendor, PONum, POAmt) VALUES
('ABC Company', 10001, 1250.00),
('DEF Company', 10002, 3254.00),
('GHI Company', 10003, 854.00),
('KLM Company', 10004, 1572.00),
('NOP Company', 10005, 6000.00);

INSERT INTO Reference1 (PONum, RefNum) VALUES
(10001, 1),
(10002, 2);

INSERT INTO Reference2 (PONum, RefNum) VALUES
(10003, 3),
(10004, 4);

INSERT INTO Invoice (RefNum, InvNum, InvAmt) VALUES
(1, 'I0001', 1250.00),
(2, 'I0002', 3254.00),
(3, 'I0003', 854.00),
(4, 'I0004', 1572.00);

CREATE TABLE



CREATE TABLE



CREATE TABLE



CREATE TABLE



INSERT 0 5



INSERT 0 2



INSERT 0 2



INSERT 0 4


select po.Vendor,
       po.PONum, 
       po.POAmt, 
       i.InvNum, 
       coalesce(i.InvAmt, 0) as InvAmt
  from po
  left
  join
       (select *
          from reference1
         union
        select *
          from reference2
       ) r
    on po.poNum = r.poNum
  left
  join Invoice i
    on r.refNum = i.refNum


vendor
ponum
poamt
invnum
invamt


KLM Company
10004
1572.00
I0004
1572.00

DEF Company
10002
3254.00
I0002
3254.00

ABC Company
10001
1250.00
I0001
1250.00

GHI Company
10003
854.00
I0003
854.00

NOP Company
10005
6000.00
null
0



SELECT 5


SELECT * FROM(
SELECT t1.Vendor,t1.PONum, t1.POAmt, t3.InvNum, t3.InvAmt  FROM PO t1
LEFT JOIN Reference1 t2 ON t2.PONum = t1.PONum
LEFT JOIN Invoice t3 ON t3.RefNum = t2.RefNum
UNION ALL
SELECT t1.Vendor,t1.PONum, t1.POAmt, t3.InvNum, t3.InvAmt  FROM PO t1
LEFT JOIN Reference2 t2 ON t2.PONum = t1.PONum
LEFT JOIN Invoice t3 ON t3.RefNum = t2.RefNum
)x


vendor
ponum
poamt
invnum
invamt


ABC Company
10001
1250.00
I0001
1250.00

DEF Company
10002
3254.00
I0002
3254.00

NOP Company
10005
6000.00
null
null

GHI Company
10003
854.00
null
null

KLM Company
10004
1572.00
null
null

GHI Company
10003
854.00
I0003
854.00

KLM Company
10004
1572.00
I0004
1572.00

DEF Company
10002
3254.00
null
null

NOP Company
10005
6000.00
null
null

ABC Company
10001
1250.00
null
null



SELECT 10


Vendor         PONum     Amt        InvNum   Amt
ABC Company    10001      1,250.00   I0001    1,250.00
DEF Company    10002      3,254.00   I0002    3,254.00
GHI Company    10003        854.00   I0003      854.00
KLM Company    10004      1,572.00   I0004    1,572.00
NOP Company    10005      6,000.00                0.00

ERROR:  syntax error at or near "Vendor"
LINE 1: Vendor         PONum     Amt        InvNum   Amt
        ^
vendor	ponum	poamt	invnum	invamt
KLM Company	10004	1572.00	I0004	1572.00
DEF Company	10002	3254.00	I0002	3254.00
ABC Company	10001	1250.00	I0001	1250.00
GHI Company	10003	854.00	I0003	854.00
NOP Company	10005	6000.00	null	0