我要实现的效果如下面的语句,就是希望查出用户名为zhang的用户(可能会有几条记录哦)对应的部门ID以及该部门的所有下属部门。但下面语句子查询中的a.DepartID这里是会报错的,应该如何修改?
select b.DepartID,b.DepartName
from UserInfo a
left join (select x.* from Departments x start with DepartID=a.DepartID connect by prior DepartID=UpdepartID) b on b.UseChk=1
where a.UserID='zhang'
select b.DepartID,b.DepartName
from UserInfo a
left join (select x.* from Departments x start with DepartID=a.DepartID connect by prior DepartID=UpdepartID) b on b.UseChk=1
where a.UserID='zhang'
from departments b,userinfo a
where b.departid=a.departid(+)
and a.userid(+)='zhang'
start with a.userid is not null
connect by prior b.departid=b.updepartid
from UserInfo a
left join (select x.* from Departments x connect by prior DepartID=UpdepartID) b on b.UseChk=1 and a.DepartID=b.DepartID
where a.UserID='zhang'
select b.departid,b.departname
from departments b
left join userinfo a
on b.departid=a.departid
where a.userid ='zhang'
start with a.userid is not null
connect by prior b.departid=b.updepartid