SELECT     dg2.dev_group_type_id, dg2.dev_group_id, dg2.dev_group_name, dg2.dev_group_l, dg2.dev_group_r,
                CASE WHEN dg2.dev_group_parentid = 0 THEN (- 1 * dg2.dev_group_type_id) ELSE dg2.dev_group_parentid END AS dev_group_parentid, dg2.dev_group_date_created,
            dev_group_auto
            , map.map_id
FROM       device_group dg1
                       JOIN device_group_type on dg1.dev_group_type_id = device_group_type.dev_group_type_id
                       JOIN device_group dg2 ON dg1.dev_group_type_id = dg2.dev_group_type_id AND dg1.dev_group_l >= dg2.dev_group_l AND
                      dg1.dev_group_l <= dg2.dev_group_r
                      left join map on dg2.dev_group_id = map.dev_group_id and dg2.dev_group_type_id = map.dev_group_type_id
--                       where dg2.dev_group_parentid = 16
GROUP BY dg2.dev_group_type_id, dg2.dev_group_id, dg2.dev_group_name, dg2.dev_group_l, dg2.dev_group_r,
                      CASE WHEN dg2.dev_group_parentid = 0 THEN (- 1 * dg2.dev_group_type_id) ELSE dg2.dev_group_parentid END, dg2.dev_group_date_created,  dev_group_auto, map_id