SQL查询结果异常、 向高手讨教。
 
 

SQL code

        select * from  dbo.tbl_staffMove             ←全查语句       联查语句↓

select sm.id,st.uname,dp.name as oldname,dp1.name as newdname, sm.kind,sm.remark,sm.createTime,ad.loginName 
from tbl_staffMove as sm 
inner join tbl_staff as st on st.id = sm.staffId  
left join tbl_admin as ad on ad.id = sm.adminId     
left join tbl_departMent as dp on dp.id = sm.OldDepart     --从调动表中获取原部门 
left join tbl_departMent as dp1 on  dp1.id = st.departId   --从人员表中获取新部门

  查询结果 全查结果
  id staffId oldDepart newDepart kind remark adminId createTime
-------------------------------------------------------- 
  35 47 11 0 0 NULL 2011-12-23 00:00:00.000
  36 48 11 0 0 2 2011-12-15 00:00:00.000
  37 50 18 0 0 ss NULL 2011-12-01 00:00:00.000

  联查结果
  id uname oldname newdname kind remark createTime loginName
-------------------------------------- ---------------------
  35 张辉 项目小组xiao 项目组 0 2011-12-23 00:00:00.000 NULL
  36 张凯 项目小组xiao NULL 0 2011-12-15 00:00:00.000 aa
  37 张三丰 朔明电子 项目小组xiao 0 2011-12-01 00:00:00.000 NULL


  各位大哥大姐们... 叔叔阿姨们.. 上面全查出来的 newDepart 和地下的 newdname 为神马就不一样? 地下的应该全部是NULL 啊、 




------解决方案--------------------

SQL code

select sm.id,st.uname,dp.name as oldname,dp1.name as newdname, sm.kind,sm.remark,sm.createTime,ad.loginName 
from tbl_staffMove as sm 
inner join tbl_staff as st on st.id = sm.staffId  
left join tbl_admin as ad on ad.id = sm.adminId     
left join tbl_departMent as dp on dp.id = sm.OldDepart     --从调动表中获取原部门 
left join tbl_departMent as dp1 on  dp1.id = sm.newDepart  --从人员表中获取新部门