I'm learning postgresql and I can't figure out how to write this nested query or if it's even possible.
Table -> Points: pid | val
Table -> Grid: gid | max_val | min_val
Return I want: pid | gid | val
This is how I see it in my head
for p in (SELECT * FROM Points)
SELECT gid FROM Grid AS g WHERE p.val < g.max_val AND p.val > g.min_val
Is this possible? If so can someone point me in the right direction?
I wouldn't think you need a nested query, just a join. Does something like this give what you need?
SELECT
P.pid,
G.gid,
P.val
FROM
Grid AS G
INNER JOIN
Points AS P
ON P.val < g.max_val
AND P.Val > g.min_val
Following query will always return point even if point's val is not in any grid range - in such case gid will be NULL:
SELECT
p.pid,
g.gid,
p.val
FROM
Points p
LEFT JOIN Grid g ON g.min_val > p.val AND g.max_val < p.val
If there is more than one Grid with a range that will match the point's val, query will return two rows for each gid.
If you want to return only points matching any grid, just add a condition:
WHERE
g.gid IS NOT NULL
