Postgres Pro
Downloads
Home   >   mailing lists

Thread: select from one table with help of another table

select from one table with help of another table

From
Date:

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?

Kind regards,

Paul

Re: select from one table with help of another table

From
Laurenz Albe
Date:
On Tue, 2020-06-16 at 08:37 +0000, paul.malm@lfv.se wrote:
> I’m trying to get the fid (integer) of objects in table line where the geometry (postgis) is inside another geometry
fromanother 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?

What if a "line" is contained in more than one "polugon", and
in one of these it has the highest "fid", but not in the others?

Should that be included in the results?

Yours,
Laurenz Albe

Re: select from one table with help of another table

From
"David G. Johnston"
Date:
On Tue, Jun 16, 2020 at 1:38 AM <paul.malm@lfv.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.