oracel联合查询的时候遇到的小问题

[时光是琥珀]

问题是这样的，我在Ａ表里面，放了几个字段，其中有ＩＤ和department，在另一张表里面，放的是departmentID和name,其中department存放的值可以和departmentID对应起来，两个结合可以查询出A表department所对应的name,但现在我又在里面分别添加了department1，department2，department3这三个字段，分别去存放其他的部门，那我想查询出department1，department2，department3所对应的B表中的name该怎样做啊，目前我想到的是decode，但这样太麻烦了，所以请教下大家有什么好的方法啊？

[没什么是无所不能的]

select A.Id, B.Name, B1.Name as Name1, B2.Name as Name2, B3.Name as Name3 from A, B, B B1, B B2, B B3 where A.department = B.departmentid and A.department1 = B1.departmentid and A.department2 = B2.departmentid and A.department3 = B3.departmentid;

[IceIsabel]

为什么部门ID与部门名称要分两个表存放？

[时光是琥珀]

引用 1 楼 wenyzh 的回复:

select A.Id, B.Name, B1.Name as Name1, B2.Name as Name2, B3.Name as Name3 from A, B, B B1, B B2, B B3 where A.department = B.departmentid and A.department1 = B1.departmentid and A.department2 = B2.departmentid and A.department3 = B3.departmentid;

我用nvl函数解决的