public inbox for [email protected]
help / color / mirror / Atom feedselect from one table with help of another table
3+ messages / 3 participants
[nested] [flat]
* select from one table with help of another table
@ 2020-06-16 08:37 [email protected]
2020-06-16 12:03 ` Re: select from one table with help of another table Laurenz Albe <[email protected]>
2020-06-16 15:05 ` Re: select from one table with help of another table David G. Johnston <[email protected]>
0 siblings, 2 replies; 3+ messages in thread
From: [email protected] @ 2020-06-16 08:37 UTC (permalink / raw)
To: pgsql-novice
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select from one table with help of another table
2020-06-16 08:37 select from one table with help of another table [email protected]
@ 2020-06-16 12:03 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: Laurenz Albe @ 2020-06-16 12:03 UTC (permalink / raw)
To: [email protected]; pgsql-novice
On Tue, 2020-06-16 at 08:37 +0000, [email protected] wrote:
> 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?
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: select from one table with help of another table
2020-06-16 08:37 select from one table with help of another table [email protected]
@ 2020-06-16 15:05 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2020-06-16 15:05 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-novice
On Tue, Jun 16, 2020 at 1:38 AM <[email protected]> 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2020-06-16 15:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-16 08:37 select from one table with help of another table [email protected]
2020-06-16 12:03 ` Laurenz Albe <[email protected]>
2020-06-16 15:05 ` David G. Johnston <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox