none
sp only view approve documents

    Question

  • 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

    2=reject

    1=approve

    3=pending

    how to solve this?


    Thursday, October 24, 2013 11:32 AM

All replies

  • 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.

    Thursday, October 24, 2013 11:43 AM
  • approve type is also table name and also column name

    look

    approve type table

    approveid

    approvetype

    Status

    so how i solve this?

    Thursday, October 24, 2013 12:05 PM
  • i want to show which documnet is approved ..

    table of approval i show above

    table of documentinfo

    Thursday, October 24, 2013 12:08 PM
  • Hi rebmaamberrebmaamber,

    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.

    Best Regards,
    Allen Li


    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.

    Wednesday, October 30, 2013 8:16 AM