请问如何实现这个查询
有表a、b、c如下
a：
id   otherid
1     b1
2     b2
3     c2

b:
id   bname
1     路人甲
2     路人乙

c:
id   cname
1     路人丙
2     路人丁

现希望在a表中otherid以b开头的就可以和bname关联，c开头的和cname关联
即查询出如下的结果：
id   otherid   name
1     b1             路人甲
2     b2             路人乙
3     c2             路人丁

------解决方案--------------------
declare @ta table(id int, otherid varchar(10))
insert @ta
select 1, 'b1 ' union all
select 2, 'b2 ' union all
select 3, 'c2 '
declare @tb table(id int, bname varchar(10))
insert @tb
select 1, '路人甲 ' union all
select 2, '路人乙 '
declare @tc table(id int, cname varchar(10))
insert @tc
select 1, '路人丙 ' union all
select 2, '路人丁 '

select a.id,a.otherid,isnull(b.bname,c.cname) as name
from @ta as a
left join @tb as b on a.otherid = 'b ' + rtrim(b.id)
left join @tc as c on a.otherid = 'c ' + rtrim(c.id)

/*结果
id otherid name
----------- ---------- ----------
1 b1 路人甲
2 b2 路人乙
3 c2 路人丁
*/

------解决方案--------------------
select a.id,a.otherid,isnull(bname,cname) as name
from a left outer join b
on replace(a.otherid, 'b ', ' ')=b.id
left outer join c
on replace(a.otherid, 'c ', ' ')=c.id
------解决方案--------------------
create table a (id int,otherid varchar(10))
insert a
select 1, 'b1 'union all
select 2, 'b2 'union all
select 3, 'c2 '

create table b (id int,bname varchar(10))
insert b
select 1, 'bskh 'union all
select 2, 'bsjh '

create table c (id int,cname varchar(10))
insert c
select 1, 'csdsh 'union all
select 2, 'ccdjh '

select a.id,a.otherid,sname = (case substring(otherid,1,1) when 'b ' then bname
else cname end ) from a,b,c
where cast(substring(otherid,2,1) as int) = b.id and cast(substring(otherid,2,1) as int) = c.id


drop table a
drop table b
drop table c


id otherid sname
----------- ---------- ----------
1 b1 bskh
2 b2 bsjh
3 c2 ccdjh
------解决方案--------------------
select a.id,a.otherid,name=case when left(a.otherid,1)= 'b ' then b.otherid
when left(a.otherid,1)= 'c ' then c.otherid end from a left join b
on right(a.otherid,1)=rtrim(b.id) left join c on right(a.otherid,1)=rtrim(c.id)