Re: select from one table with help of another table

On Tue, Jun 16, 2020 at 1:38 AM <paul(dot)malm(at)lfv(dot)se> wrote:

> Hi, list,
>
> I’m trying to get the fid (integer) of objects in table line where the
> geometry (postgis) is inside another geometry from another table (polygon),
>
> But I would not like to get the objects with the highest fid inside the
> polygons of table buffered.
>
> I’ve tried this:
>
> SELECT fid FROM
>
> "line" USING “polugon” AS b WHERE
>
> ST_Contains(b.geom, "line".geom) AND "line".fid NOT IN (SELECT
> MAX("line".fid)
>
> );
>
>
>
> It complains about “USING” in line 2.
>
> Anyone who knows how I should do it instead?
>

Learning the basics of select queries combining multiple tables using joins
is probably better done by reading (or watching videos). The documentation
does cover this a bit in its tutorial.

https://www.postgresql.org/docs/12/tutorial-join.html

Then the SQL Command section for SELECT shows the formal syntax for a FROM
clause:

[ FROM from_item [, ...] ]
...
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
...and/or...
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING (
join_column [, ...] )

You will notice that USING in the context of a SELECT's FROM accepts column
names and is used as part of an explicit JOIN

[not tested]
SELECT fld FROM line JOIN polugon AS b USING (matching column names, uses
equality)

Though from your example the join doesn't seem to be equality based
(ST_Contains) so you probably have to use the "ON join_condition" syntax
(and remove the condition ST_Contains from the WHERE clause)

Or replace the USING with a comma (per FROM from_item, from_item) and
continue to evaluate the join condition (ST_Contains) in the WHERE clause.

Personal preference is to be explicit with joins and keep join conditions
attached to the join clauses and leave where clauses for non-join
conditions.

David J.