There are actually a couple more options, but the one that you were likely looking for is called a correlated subquery.  The subquery can reference values from the outer query, so there is no need for a join.  (NOTE: This query will produce an error if there are multiple rows in TableThree that meet the criteria.)

select A.name
  ,(select C.ID from TableThree C WHERE A.id=C.id AND A.id=2)
from TableOne A
left join TabletTwo B
on A.id=B.id

Drew