sql查询 - MS-SQL Server / 疑难问题

有个表 属性有员工的编号ID 员工的名字name;
另外一个表里面有部门的编号tID 部门的名字tname,部门经理的编号MID,总监的名字DId;
现在要新建一个view 里面有部门的编号,部门的名字 部门经理的名字 和总监的名字
现有的sql语句:
select tba.tID,tba.tname,tba.MName,tbb.DName
from
(select b.tID,b.tname,a.name as MName
from a,b
where a.ID = b.MID) as tba,
(select b.tID,b.tname,a.name as DName
from a,b
where a.ID = b.DId) as tbb
where tba.tID = tbb.tID
但发现效率不太高,请问有什么高效的方法吗

---

SF是我的

---

MS不需要嵌套

---

等待高手解答 帮顶！

---

SQL code:
select b1.tid.b1.tname,b2.mname,b2.dname
from a a
left join b b1 on a.id=b.mid
left join b b2 on a.id=b.did
where b1.tid=b2.tid

---

SQL code:
--貌似这样就可以查出来了
select
tid,tname,mid,did
from
tb

---

SQL code:
要不然是这样?
select
b1.tid.b1.tname,b2.mname,b2.dname
from
tb a
left join
tb b1 on a.id=b.mid
left join
tb b2 on a.id=b.did
where
b1.tid=b2.tid

---

SQL code

---

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.