Select 
... (around 40 items)...
from
ATable a
left outer join a.subA SubA
left outer join a.subB SubB
left outer join a.Names Names
left outer join Names.Addresses Addresses
left outer join Names.Phones Phones
left outer join a.Roles Roles
where
a.aType.someType in ('...')
and (Names.id.FKey in('...') or Names.id.FKey is null) 
and upper(a.pin) like upper('some%') 
and upper(Names.lastName) like upper('Some%') 
order by  a.pin    asc