select
rp.store_code,
rpp.id,
rpp.type,
rpp.street,
rpp.zip,
ra.name as areaname,
rc.name AS cityname,
rs.name AS statename
from
res_partner as rp
left join res_partner as rpp
on rp.id = rpp.parent_id 
left join res_area as ra on ra.id = rpp.area_id
left join res_country_state as rs on rs.id = rpp.state_id
left join res_city as rc on rc.id = rpp.city_id
WHERE  rpp.parent_id in(SELECT id FROM res_partner WHERE cust_type = 'zota' and COALESCE(store_code,'')!='')
and COALESCE(rpp.state_id,0)>0
UNION ALL
select
rp.store_code,
rp.id,
rp.type,
rp.street,
rp.zip,
ra1.name as areaname,
rc1.name AS cityname,
rs1.name AS statename
from
res_partner as rp
left join res_area as ra1 on ra1.id = rp.area_id
left join res_country_state as rs1 on rs1.id = rp.state_id
left join res_city as rc1 on rc1.id = rp.city_id
where rp.id in(SELECT id FROM res_partner WHERE cust_type = 'zota' and COALESCE(store_code,'')!='') 
      and COALESCE(rp.state_id,0)>0