How to fix Column does not exist on this Inner Join of two tables with three conditions?

Question

Currently, I am trying to create a new table based on the Inner Join query of two tables with three conditions. However, the SQL Error window always tells me the columns does not exist even when they clearly do.

So this is what has to happen an Inner Join has to happen when two specific values are equal to each other in the table of the columns and where are third value shares a similarity.

This is because while the Plotletter in the first table is actually only one letter like for say A. The Application could be written like ABCD.

I have already also tried to make the clear the fields are referred to the right table by following the suggestion, but the error still happens.

CREATE TABLE testschema.FinalPlantenpaspoort 
AS
SELECT PrimaryIndex, jaarpr, proefcode, plotleter, plotcijfer, plot, X, Y
FROM testschema.plantenpaspoortsjabloon
  JOIN testschema.weegschaalproeven
    ON plantenpaspoortsjabloon.proefcode = weegschaalproeven.Intern_Proef_Nr 
   AND plantenpaspoortsjabloon.plotcijfer = weegschaalproeven.Objectnr 
WHERE plantenpaspoortsjabloon.plotletter LIKE weegschaalproeven.Application
;

This the error and suggestion they give me but no luck.

ERROR:  column weegschaalproeven.intern_proef_nr does not exist
LINE 5: ON plantenpaspoortsjabloon.proefcode = weegschaalproeven.Int...
                                               ^
HINT:  Perhaps you meant to reference the column "weegschaalproeven.Intern_Proef_Nr".
SQL state: 42703
Character: 237

**

Edit 2/07/2019: PROBLEM HAS BEEN SOLVED BUT 0 RECORDS Selected.

** Okay the problem seems to be solved but there is a new kind of problem while the code does works 0 records are selected because of the JOIN. And this should not be the case. I know there are records that matches because this a test where I made sure the tables of the shapefiles in QGIS contains data that is relevant.

Create TABLE testschema.finalplantenpaspoort AS
SELECT jaarpr, proefcodet, plotletter, plotcijfer, plot, X, Y
FROM testschema.plantenpaspoortsjabloon
JOIN testschema.weegschaalproeven
ON plantenpaspoortsjabloon.proefcodet = weegschaalproeven.intern_proef_nr AND plantenpaspoortsjabloon.plotcijfer = weegschaalproeven.objectnr 
WHERE plantenpaspoortsjabloon.plotletter LIKE weegschaalproeven.application
;
**SELECT 0**

Query returned successfully in 72 msec.

`Intern_Proef_Nr` is a different column name than `"Intern_Proef_Nr"`. https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS — , Jul 01 '19 at 14:33
Yes, you need to double quote the column name as it gets converted to lowercase otherwise. One of the annoyances of using capital letters in your table or column names. — J Spratt, Jul 01 '19 at 14:38
In Postgresql my suggestion would be to avoid capitals, if you want columsn etc to be readable: FinalPlantenpaspoort change to final_planten_paspoort — Paul, Jul 02 '19 at 09:14
Thank you all for the response but I have a new error, that I will add in the edit. — ThunderSpark, Jul 02 '19 at 13:00
What's unclear about the new error message? Postgres will refuse to compare apples (=numbers) to oranges (=strings). Storing numbers in a `varchar` column is a really, really bad idea to begin with. — , Jul 02 '19 at 13:06
Nice to know for the next time, the problem has been solved by altering the table, however my querry selects 0 records. Which should not be the case. — ThunderSpark, Jul 02 '19 at 13:25

score 0 · Accepted Answer · answered Jul 03 '19 at 11:42

I have found the sollution to my zero select problem, apparently I needed to set a special Like circumstance because otherwise the records could not be matched here it goes:

Create TABLE testschema.finalplantenpaspoort AS
SELECT proefcodet, proefnaam, datumvernietiging, oogstvernietigingsmethode, objectnr, productcode, potnummer, dosis, oppervlakte, eenheid, luikb, oogstbestemming, application, opmerking, proefjaar, proefcode, plotletter, plotcijfer, plot, X, Y 
FROM testschema.plantenpaspoort 
JOIN testschema.weegschaalproeven
ON plantenpaspoort.proefcode = weegschaalproeven.internproefnr AND plantenpaspoort.plotcijfer = weegschaalproeven.objectnr AND plantenpaspoort.plotletter LIKE ANY (regexp_split_to_array(weegschaalproeven.application , '\s*'))
;
GRANT ALL ON TABLE testschema.finalplantenpaspoort TO test_admin_test WITH GRANT OPTION;

How to fix Column does not exist on this Inner Join of two tables with three conditions?

Edit 2/07/2019: PROBLEM HAS BEEN SOLVED BUT 0 RECORDS Selected.

1 Answers1