I need to write query where i need to find devices that fit in specific coordinates, and then extend the results with some devices that are in the same groups as the one that are visible on the map.
The part where i can find the devices and their fitting groups is an easy task where i just perform a left join on both tables like this:
SELECT *
FROM device
LEFT JOIN group
ON device.group_id = group.id
WHERE geo_location && ST_MakeEnvelope(10, 30, 30, 50)
But i’m not sure what is the efficient way to add devices to the result that belong to groups of visible devices.
One idea was to select distinct groups from the illustrated query and then again query the device table for the ones that fit in those groups, but it seems to me that this is not the right way.
Any opinion and help is appreciated.
>Solution :
If you don’t need to retrieve any data from the group table, then you can do a self join on table devices while testing the rows with the same group_id :
SELECT g.*
FROM device AS d
INNER JOIN devices AS g
ON g.group_id = d.group_id
WHERE d.geo_location && ST_MakeEnvelope(10, 30, 30, 50)