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 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 (
> 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