求一条SQL语句 - MS-SQL Server / 基础类

现有
  表A 表B
id name firid secid thiid
1 军1 1 3 5
2 军2 1 3 6
3 师1 2 4 7
4 师2
5 旅1
6 旅2
7 旅3

想得到如下结果
fir sec thi
军1 师1 旅1
军1 师1 旅2
军2 师2 旅3

怎样实现呢？谢谢！

---

SQL code:
SELECT A1.[NAME],A2.[NAME],A3.[NAME]
from B
INNER JOIN A A1 ON B.FIR=A1.ID
INNERJ OIN A A2 ON B.SEC=A2.ID
INNER JOIN A A3 ON B.THI=A3.ID

---

SQL code:
SELECT A1.[NAME],A2.[NAME],A3.[NAME]
from B
INNER JOIN A A1 ON B.FIR=A1.ID
INNER JOIN A A2 ON B.SEC=A2.ID
INNER JOIN A A3 ON B.THI=A3.ID

---

测试
SQL code:

create table ta (id int,name varchar(3))
insert ta
select 1 ,'军1' union all
select 2 ,'军2' union all
select 3 ,'师1' union all
select 4 ,'师2' union all
select 5 ,'旅1' union all
select 6 ,'旅2' union all
select 7 ,'旅3'


create table tb(firid int, secid int,thiid int)
insert tb
select 1,3,5 union all
select 1,3,6 union all
select 2,4,7


select t1.name fir,t2.name sec,t3.name thi
from tb,ta t1