Search code examples
postgresqljoinleft-joininner-join

Subquery to join conversion

How do I convert this with a query to inner join or any kind of join? this query is working fine with WITH in SQL.

with trr as(SELECT DISTINCT td.* FROM "groups" g inner join
"territoryDetails" td on td."groupId" = g.id where g."orgId" = 13),
tdd as(select trr."groupId" from contacts c inner join trr on
ST_Intersects(trr.points,c."geoPoint") where c.id = 567 and
c."orgId"=130) select * from tdd;
Solution

  • Well your current CTE based query actually is already using joins, but I suspect that the use of CTEs itself is the issue here. You could refactor the query by inlining and removing all the CTEs:

    SELECT trr.groupId
FROM contacts c
INNER JOIN
(
    SELECT DISTINCT td.*
    FROM groups g
    INNER JOIN territoryDetails td ON td.groupId = g.id
    WHERE g.orgId = 13
) trr
    ON ST_Intersects(trr.points, c.geoPoint)
WHERE
    c.id = 567 AND c.orgId = 130;