I have 2 tables one with points as geographies and other with polygons as geographies. I am able to find which polygon a single point falls(from the point table) by the following query:

DECLARE @p geography;
select @p = PointGeom from dbo.PointTable where ID = 1 
SELECT  a.ID, ATTRIBUTE1, geom 
from dbo.PolygonTable  a
where geom.STIntersects(@p) = 1;

However, I want to do a join between the two tables and get the polygons in which each of the points in the Point Table fall. Is it even possible? Or do I need to loop through the Point table and call the above query multiple times?

sql join spatial
Edit
Report