Thread: Join query
Hi,
I have two tables: establishment which contains these columns: id, name, longitude, latitude, geom (Geometric column)
Product contains: id, name, establishment_id
First of all I want to select the establishment within a radius.
I run this query:
from establishment as e, establishment as e1
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000)
The result of this query returns all the establishment within a radius 1KM from from a given establishment which has an id = 1.
After that I want to get the product's name of each establishment from the query's result.
Is there an other optimized solution to make a query such this:
select * from (
from establishment as e, establishment as e1
and e1.id = 1
and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000)) as tmp inner join product as p on p.establishment_id = tmp.id
hmidi slim wrote: > I have two tables: establishment which contains these columns: id, name, longitude, latitude, geom (Geometric column) > Product contains: id, name, establishment_id > First of all I want to select the establishment within a radius. > I run this query: > select e.name, e1.name > from establishment as e, establishment as e1 > where e.id <> e1.id > and e1.id = 1 > and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000) > > The result of this query returns all the establishment within a radius 1KM from from a given establishment which has anid = 1. > > After that I want to get the product's name of each establishment from the query's result. > > Is there an other optimized solution to make a query such this: > select * from ( > select e.name, e1.name, e.id > from establishment as e, establishment as e1 > where e.id <> e1.id > and e1.id = 1 > and ST_DWithin(geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000)) as tmp inner join product as p on p.establishment_id= tmp.id A simple join is what you need: SELECT e.name, e1.name FROM establishment AS e JOIN establishment AS e1 ON ST_DWithin(e.geom, ST_MakePoint(e1.longitude, e1.latitude)::geography, 1000) AND e.id <> e1.id JOIN product AS p ON p.establishment_id = e.id WHERE e1.id = 1; Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com