i try to show both approve and reject documents beacuse user view which documnets is approve and reject but the query which i have tried only show approved documnets how to also get reject document have a look this query
Alter procedure ddddd @Userid as SELECT DISTINCT DocumentInfo.DocID as DocumentID , dbo.DocumentInfo.DocName as DocumentName, dbo.DocumentInfo.Uploadfile as FileUploaded, dbo.DocType.DocType as Document, dbo.Department.DepType as Department , dbo.ApproveType.ApproveType AS ApproveID FROM dbo.DocumentInfo inner JOIN dbo.DocType ON dbo.DocumentInfo.DocTypeID=dbo.DocType.DocTypeID inner JOIN dbo.Department ON dbo.DocumentInfo.DepID=dbo.Department.DepID LEFT JOIN dbo.ApproveType ON dbo.ApproveType.approveid=dbo.Department.DepID LEFT OUTER JOIN Approval a ON a.DocID = a.DocID JOIN ApproveType at ON at.ApproveID = ISNULL(a.Approveid, 3) where UserID=@userid
record in table
SeqNo DocID ApproveID ApproveBy
when i test the query
how to solve this?
- Edited by rebmaamberrebmaamber Thursday, October 24, 2013 11:34 AM
Use table alias names to make your query readable:
DECLARE @userid INT = 1; SELECT DISTINCT DI.DocID AS DocumentID , DI.DocName AS DocumentName , DI.Uploadfile AS FileUploaded , DT.DocType AS Document , D.DepType AS Department , AT.ApproveType AS ApproveID FROM dbo.DocumentInfo DI INNER JOIN dbo.DocType DT ON DI.DocTypeID = dbo.DT.DocTypeID INNER JOIN dbo.Department D ON DI.DepID = D.DepID LEFT JOIN dbo.ApproveType AT1 ON AT1.approveid = D.DepID LEFT JOIN Approval A ON A.DocID = A.DocID INNER JOIN ApproveType AT2 ON AT2.ApproveID = ISNULL(A.Approveid, 3) WHERE UserID = @userid;
Now we can see some serious flaws:
LEFT JOIN Approval A ON A.DocID = A.DocID
The JOIN condition above makes no sense. It is a tautology, thus you have generated a Cartesian product.
LEFT JOIN dbo.ApproveType AT1 ON AT1.approveid = D.DepID INNER JOIN ApproveType AT2 ON AT2.ApproveID = ISNULL(A.Approveid, 3)
Why do you JOIN on ApproveID and DepID (department)? Big mistake. And Why do you JOIN the ApproveType twice? And what is that ISNULL() INNER JOIN condition for? Also the naming indicates that the condition should be AT2.ApproveTypeID = A.ApproveTypeID otherwise I would diagnose an erroneous data model.
btw, a concise and complete example would shed more light into this.
To troubleshoot this kind of issue, we can remove all the join tables and add one after another to find in which step the records are removed. Additionally, in order to find a solution quickly, please post your table definition codes and some sample data here for analysis.
We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
Thanks for helping make community forums a great place.
- Proposed as answer by Allen Li - MSFTMicrosoft contingent staff, Moderator Sunday, November 03, 2013 2:11 PM