PostgreSQL ERROR: syntax error at or near "WHERE"

Question

i have to create a simple view in Postgres but somthing goes wrong.

i will paste my SQL query below:

CREATE OR REPLACE VIEW domotica."sensori-stanze-unita"
 AS
SELECT
sens.id_sens,sens.nome,sens.id_stanza,stanz.id,stanz.id_immobile,immo.id
FROM domotica.sensori AS sens
INNER JOIN domotica.stanze AS stanz
INNER JOIN domotica.immobili AS immo
ON stanz.id_immobile = immo.id 
WHERE sens.id_stanza = stanz.id;

The error displayed is the following

ERROR: syntax error at or near "WHERE"
LINE 9: WHERE sens.id_stanza = stanz.id;
^

Answer 1

An INNER JOIN requires a ON clause, that usually contains the join conditions. Here, you can just move the conditions from the WHERE clause to the ON clause of the first join:

CREATE OR REPLACE VIEW domotica."sensori-stanze-unita" AS
SELECT se.id_sens, se.nome, se.id_stanza, st.id AS st_id, st.id_immobile, im.id as im_id
FROM domotica.sensori AS se
INNER JOIN domotica.stanze AS st ON se.id_stanza = st.id
INNER JOIN domotica.immobili AS im ON st.id_immobile = im.id