Re: Join query

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