suppose there are two tables
1_t (a,b,c)
2_t(d,e)
and other_name means to have the alias for the records chose

select a, b
(select d from 2_t where d=1 and e=2) other_name,

from 1_t
where b between 1 and 2
and c = 3
order by a, b;