SELECT
x.Name,
base.ls_id,
base.date_lease_start,
base.date_lease_end,
base.lease_term,
base.amount_current_rent
FROM base_table AS base
LEFT OUTER JOIN thing1_table AS thing1 ON base.as_id = thing1.as_id
LEFT OUTER JOIN thing2_table AS thing2 ON base.bl_id = thing2.bl_id
LEFT OUTER JOIN thing3_table AS thing3 ON base.ld_id = thing3.ld_id
CROSS APPLY (SELECT Name = COALESCE(thing1.name, thing2.name, thing3.name)) x
WHERE x.name = @SearchVal
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden