Re: select from one table with help of another table
| От | David G. Johnston |
|---|---|
| Тема | Re: select from one table with help of another table |
| Дата | |
| Msg-id | CAKFQuwbcfsa82J-W+6kD3Mzx4Pj8btofPtzSDJBoubiE7gDp0A@mail.gmail.com обсуждение исходный текст |
| Ответ на | select from one table with help of another table (<paul.malm@lfv.se>) |
| Список | pgsql-novice |
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.
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.
В списке pgsql-novice по дате отправления: