SQL Spatial select record where intersects another record

Question

I have two tables - 'Tree' (point) and 'ServiceArea' (polygon) in the GIS database. Trying to select all Tree records that intersect a ServiceArea where the value in Tree.ServiceAreaNumber field doesn't equal the value in the intersecting ServiceArea.ServiceAreaNumber field.

Something like this:

SELECT
    T.TreeID,
    T.ServiceAreaNumber AS RecordedServiceArea,
    S.ServiceAreaNumber AS ActualServiceArea
FROM
    TREE T,
    SERVICEAREA S
WHERE
    T.STIntersect(S) AND T.ServiceAreaNumber != S.ServiceAreaNumber

The above code is how I see it working when I think about it, I just haven't been able to find any examples of how to actually do a spatial query between two tables. All the examples I've found have declared a textual point:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('LINESTRING(0 2, 2 0, 4 2)', 0);
SET @h = geometry::STGeomFromText('POINT(1 1)', 0);
SELECT @g.STIntersects(@h);

EDIT: Perhaps I need to look something along the lines of

declare @polygon geometry = 'POLYGON((-9486683.581 4810152.256, -9282073.762 4821688.121, -9262037.786 4625578.413, -9477576.319 4628614.167, -9486683.581 4810152.256))'
select * from tablename 
where tablename.geom.STIntersects(@polygon) = 1

from SQL Server Spatial Querying however I'm unsure how to specify the second table for the intersect rather than declaring a single feature.

Answer 1

Answering my own question. In the WHERE clause I had to change T.STIntersect(S) to T.Shape.STIntersects(S.Shape) = 1

Full example:

SELECT
    T.TreeID,
    T.ServiceAreaNumber AS RecordedServiceArea,
    S.ServiceAreaNumber AS ActualServiceArea
FROM
    TREE T,
    SERVICEAREA S
WHERE
    T.Shape.STIntersects(S.Shape) = 1 AND T.ServiceAreaNumber != S.ServiceAreaNumber