I have a SQL query which is failing, giving me the error:
"There is an entry for table but it cannot be referenced from this part of the query"
from trips, test
inner join boats on boats.id = trips.boat_id
where st_intersects(trips.geom, test.geom) and
boats.uid = 44
Simple rule: Never use commas in the
FROM clause. Always use explicit
JOIN syntax . . . even if that means writing out
As mentioned in the comments, the right way to write the query is:
select trips.* from trips inner join test on st_intersects(trips.geom, test.geom) inner join boats on boats.id = trips.boat_id where boats.uid = 44;
However, the question is why this doesn't work:
from trips, test inner join boats on boats.id = trips.boat_id
First, I want to note that the failure of this syntax is better described in the MySQL documentation than in the Postgres documentation:
However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.
I think a simpler way of describing this is that
JOIN is an operator that acts on the tables/expressions that are its arguments. The comma separates the arguments. So, the first table simply isn't recognized because the comma "blocks" it.
I think you can get around this using parentheses:
from (trips, test inner) join boats on boats.id = trips.boat_id
You can definitely get around it using
from trips cross join test inner join boats on boats.id = trips.boat_id
But why bother? There is a correct way to write the query.