我使用此存储过程来显示文档处于待处理状态
create procedure b
as
select
dbo.DocumentInfo.DocID, dbo.DocumentInfo.DocName,
dbo.DocumentInfo.Uploadfile, dbo.DocType.Doc Type,
dbo.Department.DepType, ApproveType.ApproveType
from
dbo.DocumentInfo
left join
dbo.DocType on dbo.DocumentInfo.DocTypeID = dbo.DocType.DocTypeID
left join
dbo.Department on dbo.DocumentInfo.DepID = dbo.Department.DepID
inner join
ApproveType on DocumentInfo.ApproveID = ApproveType.ApproveID
and (ApproveType.ApproveID = 3)
但是当我检查此查询时,它无法显示任何文档..
问题出在哪儿?
您的问题是您要在联接要排除数据的表上进行直接联接(INNER JOIN)。因此,无论该列ApproveID
在表中DocumentInfo
不包含任何数据,或者有在表中没有匹配的行ApproveType
列ApproveID
。
在我看来,如果您将以这种方式使用此存储过程,则无论如何您都将错误地使用联接-而且您肯定可以考虑使用表别名,如下所示:
CREATE PROCEDURE b
AS
SELECT di.DocID,
di.DocName,
di.Uploadfile,
dt.DocType,
d.DepType,
at.ApproveType
FROM DocumentInfo di
LEFT OUTER JOIN
DocType dt ON dt.DocTypeID = di.DocTypeID
LEFT OUTER JOIN
Department d ON d.DepID = di.DepID
LEFT OUTER JOIN
ApproveType at ON at.ApproveID = di.ApproveID
AND at.ApproveID = 3
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句