Problem with How to join with subquery

Question

I'm trying to create a report in Postgresql that shows some columns from some tables with join. But one of them is subquery that to show a customer with the location. My Problem is, I always get notice like ERROR: column foo.[column] does not exist.

This is the simply what I've done. sqlfiddle.com

Maybe someone can help, thanks!

Table definitions and data:

CREATE TABLE public.tprog (
    progId integer NOT NULL,
    codeProg character varying(20) NOT NULL,
    nmProg character varying(100)
);
INSERT INTO tprog VALUES (1, '01PROG', 'Prog One');

CREATE TABLE public.tkeg (
    kegId integer NOT NULL,
    codeKeg character varying(20) NOT NULL,
    nmKeg character varying(100),
    progId integer NOT NULL
);
INSERT INTO tkeg VALUES (1, '01KEG', 'Keg One', 1);

CREATE TABLE public.mDonation (
    mDonationId integer NOT NULL,
    nmDonation character varying(100)
);
INSERT INTO mDonation VALUES 
(1, 'Flood donation'),
(2, 'Tsunami donation');

CREATE TABLE public.tDonation (
    tDonationId integer NOT NULL,
    customerId integer,
    mDonatioanId integer,
    month character varying(10)
);
INSERT INTO tDonation VALUES 
(1, 1, 1, '2019-01'), (1, 2, 1, '2019-02'), (1, 3, 2, '2019-03');

CREATE TABLE public.tmonth (
    tmonthId integer NOT NULL,
    progId integer NOT NULL,
    kegId integer NOT NULL,
    tDonationId integer
);
INSERT INTO tmonth VALUES (1, 1, 1, 1);

CREATE TABLE public.tCustomer (
    tCustomerId integer NOT NULL,
    nmCustomer character varying(30),
    loc3Id integer NOT NULL
);

INSERT INTO tCustomer VALUES 
(1, 'Dani', 1), 
(2, 'Sarah', 2), 
(3, 'Jin', 3);

CREATE TABLE public.loc1 (
    loc1Id integer NOT NULL,
    nmloc1 character varying(50)
);
INSERT INTO loc1 VALUES (1, 'Country1');

CREATE TABLE public.loc2 (
    loc2Id integer NOT NULL,
    nmloc2 character varying(100) DEFAULT NULL::character varying,
    loc1Id integer
);
INSERT INTO loc2 VALUES 
(1, 'SubCountry1 A', 1),
(2, 'SubCountry1 B', 1);

CREATE TABLE public.loc3 (
    loc3Id integer NOT NULL,
    nmloc3 character varying(100) DEFAULT NULL::character varying,
    loc2Id integer
);
INSERT INTO loc3 VALUES 
(1, 'SubCountry1 A1', 1),
(2, 'SubCountry1 A2', 1),
(3, 'SubCountry1 B1', 2);

Query that results in error:

SELECT a.nmprog, b.nmkeg, f.nmDonation, foo.nmloc1, foo.nmloc2, foo.nmloc3, foo.nmCustomer
FROM tprog a  
LEFT JOIN tkeg b ON a.progId=b.progId
LEFT JOIN tmonth d ON d.kegId=b.kegId
LEFT JOIN tDonation e ON e.tDonationId = d.tDonationId
LEFT JOIN mDonation f ON f.mDonationId = e.mDonatioanId
LEFT JOIN (
  SELECT z.nmloc1, y.nmloc2, x.nmloc3, w.nmCustomer FROM tCustomer w 
  LEFT JOIN loc3 x ON x.loc3Id=w.loc3Id
  LEFT JOIN loc2 y ON y.loc2Id=x.loc2Id
  LEFT JOIN loc1 z ON z.loc1Id=y.loc2Id
) AS foo
ON foo.tCustomerId=e.customerId
WHERE e.month LIKE '%2019-01%'
;

ERROR: column foo.tcustomerid does not exist Position: 508

Answer 1

The problem is:

Solution:

Result:

Thanks!