select * from (
select distinct lgmd.l_id
       , lgmd.lg_id
       , case
            when lgh.lg_id_in = 100344 then 'aframe'
            when lgh.lg_id_in in (100410) and l.geocode not like 'A-%' then 'cuby'
            when lgh.lg_id_in in (100007, 100006, 100801, 100802, 100803, 100804) then 'flowrack_orion'
         end as "area"
  from location_group_member_def lgmd
  JOIN location_group_hierarchy lgh ON lgmd.lg_id_in = lgh.lg_id
  AND lgh.lg_id_in in ( 100344 /* aframe */
                       , 100410 /* cuby*/
                       , 100007, 100006, 100801, 100802, 100803, 100804 /* flowrack orion*/
                     )
  LEFT JOIN location l ON l.l_id = lgmd.l_id
  WHERE area is not null) where l_id = (Select l_id from location where geocode like 'A-21-01D');