Query optimizing How to

Question

In my script I pass Object ID as querystring parameter. like ?idobject=1962

Now first I have to access from the OBJECTS table the GeoLat and GeoLng columns and I have to check again if there are other objects containing the same Geo Data

SELECT GeoLng, GeoLat
FROM OBJECTS
WHERE ID = 1962   

from above query I have to read the GeoLng and GeoLat variables and have to write the second query as follows

SELECT O.ID
FROM OBJECTS O
WHERE GeoLng = '12.72812515' /* will be taken from above query */
AND GeoLat   = '47.7794388' /* will be taken from above query */

AND EXISTS(
    SELECT ID 
    FROM InfObjects 
    WHERE ID = O.ID
    )

Can I write these 2 queries into a single query? Thanks

Answer 1

Edit: Updated based on Rich's comment.

What you need is a self-join.

SELECT O2.ID
FROM OBJECTS O1
JOIN OBJECTS O2 on O1.GeoLng = O2.GeoLng AND O1.GeoLat = O2.GeoLng
WHERE O1.ID = 1962   
AND EXISTS(
    SELECT ID 
    FROM InfObjects 
    WHERE ID = O2.ID
)

Or even more efficiently, like this:

SELECT O2.ID
FROM OBJECTS O1
JOIN OBJECTS O2 on O1.GeoLng = O2.GeoLng AND O1.GeoLat = O2.GeoLng
JOIN InfObjects I ON O2.ID = I.ID
WHERE O1.ID = 1962   

Answer 2

Staying with the "EXISTS" expressions, you could do something like this:

SELECT O.ID
FROM OBJECTS O
AND EXISTS (
    SELECT ID 
    FROM InfObjects 
    WHERE ID = O.ID
)
AND EXISTS (
    SELECT 1
    FROM OBJECTS o2
    WHERE o2.GeoLng = O.GeoLng AND o2.GeoLat = O.GeoLat AND o2.ID = 1962
)

You could also do it with an inner join instead of a correlated query, but I think this syntax should produce a sufficiently performant query as well.