0

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

  • 1
    Fieldname in subquery is nmCustomer. Whereas you try to join by tCustomerId. – Akina Jul 15 at 4:39
  • Sorry, I've update my code, see link – Ugy Astro Jul 15 at 4:44
  • Nothing changes. Your foo subquery dataset structure contains fields: nmloc1, nmloc2, nmloc3, nmCustomer. And no field with the name tCustomerId which you want to join by. – Akina Jul 15 at 4:47
  • 1
    You need to select tcustomerid in the derived table (subquery) otherwise you can't properly join: sqlfiddle.com/#!17/0a0d0/10 – a_horse_with_no_name Jul 15 at 5:39
  • Yes, thank's to all . finally works.. – Ugy Astro Jul 15 at 6:01
1

The problem is:

enter image description here

Solution:

enter image description here

Result:

enter image description here

Thanks!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.