CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
toy4fun
toy4fun

Reputation: 849

Refactoring a SQL Script

I've written this SQL Script: 

    DECLARE @location geography  
    DECLARE @radius int  

    SET @location = (SELECT Location FROM Hydrants WHERE HydrantId = 2)  
    SET @radius = (SELECT Radius FROM Hydrants WHERE HydrantId = 2)  

    SELECT * 
    FROM Sites 
    WHERE @location.STDistance(location) < @radius 
    ORDER BY SiteId ASC

I did this to refactor it

SELECT * 
FROM Sites, Hydrants 
Inner Join Hydrants.Location.STDistance(Sites.Location) < Hydrants.Radius  
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

but without luck.

Any advice will be welcome.

Upvotes: 1

Views: 135

Answers (2)

Julien May
Julien May

Reputation: 2051

Dont know the geographic features of sql 2008 but when seeing your code, the following might work:

SELECT
    Sites.*
FROM Sites
INNER JOIN Hydrants
    ON Hydrants.Location.STDistance(Sites.location) < Hydrants.Radius
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

Upvotes: 2

Joachim Isaksson
Joachim Isaksson

Reputation: 180947

I've never worked with the geography types, but this is the correct syntax of a join that does the same as your original query;

SELECT Sites.*
FROM Sites 
JOIN Hydrants 
  ON Hydrants.Location.STDistance(Sites.Location) < Hydrants.Radius  
WHERE Hydrants.HydrantId = 2 
ORDER BY Sites.SiteId ASC;

Upvotes: 0

Related Questions