Query optimization

Question

In my script, I pass the Object ID as a querystring parameter. how ?idobject=1962

Now, first I need to access from the OBJECTS table of the GeoLat and GeoLng 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 the previous query I need to read the GeoLng and GeoLat and 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 in one query? Thanks

Answer 1

Edit: Updated based on Rich comment.

What you need is self-connection.

 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 efficient, for example:

 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

Remaining with the expressions EXISTS, you can 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 can also do this with an inner join instead of a correlated query, but I think this syntax should also give a fairly efficient query.

Answer 3

 SELECT O.ID FROM OBJECTS OID INNER JOIN Objects O ON O.GeoLng = OID.GeoLng AND O.GeoLat = OID.GeoLat WHERE OID.ID = 1962 AND EXISTS( SELECT ID FROM InfObjects WHERE ID = OID.ID )