Extending primary table rows based on joined table ids from LEFT JOIN-CodePudding

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.

CodePudding user response：

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)

CodePudding user response：

You can use EXISTS:

SELECT *
FROM
    device AS DEV
    LEFT JOIN group AS GRP
    ON DEV.group_id = GRP.id
WHERE
    EXISTS
        (
            SELECT 1
            FROM device
            WHERE geo_location && ST_MakeEnvelope(10, 30, 30, 50) 
            AND group_id = DEV.group_id
        )