CODEWITHSUNDEEP
postgresqljoinleft-joininner-join
a_wolf_with_no_master
a_wolf_with_no_master

Reputation: 23

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;

Upvotes: 0

Views: 192

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521249

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;

Upvotes: 1

Related Questions