sql server - FOLLOW UP to SQL query to retrieve the latest status of a process

Question

The original question and schema are shown at the following link: SQL query to retrieve the latest status of a process

The solution provided by @mendosi was perfect. However, now that the deadline for submission is past, management wants more information. I've been able to give them the information they want using the following query (incorporating the aforementioned solution into the "EXISTS" clause):

SELECT        
    proposalPackage.proposalPackageID, refProposalType.name, proposalPackage.title, 
    [user].lastName, [user].firstName, [user].email, [user].phone, 
    proposalReviewAction.approvalTypeID
FROM
    proposalReviewAction, proposalPackage 
INNER JOIN
    refProposalType ON proposalPackage.proposalTypeID = refProposalType.proposalTypeID 
INNER JOIN
    proposalManagerAssignment ON proposalPackage.proposalPackageID = proposalManagerAssignment.proposalPackageID 
INNER JOIN
    [user] ON proposalManagerAssignment.userID = [user].userID
WHERE
    EXISTS (SELECT ls.* 
            FROM 
                (SELECT 
                     r.proposalPackageID, r.approvalTypeID,
                     RowNr = ROW_NUMBER() OVER (PARTITION BY r.proposalPackageID ORDER BY r.reviewedDate DESC) 
                 FROM 
                     proposalReviewAction AS r
                 JOIN 
                     proposalPackage AS pp ON pp.proposalPackageID = r.proposalPackageID
                 WHERE 
                     pp.proposalCallID = 7) AS ls
            WHERE 
                ls.RowNr = 1
                AND (ls.approvalTypeID = 50))
GROUP BY 
    proposalPackage.proposalTypeID, [user].lastName, [user].firstName,
    [user].email, [user].phone, proposalPackage.title, 
    refProposalType.name, proposalManagerAssignment.isPrimary, 
    proposalPackage.proposalCallID, approvalTypeID, 
    proposalPackage.proposalPackageID, proposalReviewAction.approvalTypeID
HAVING        
    (proposalManagerAssignment.isPrimary = 1) 
    AND (proposalPackage.proposalCallID = 7) 
    AND (approvalTypeID = 50)
ORDER BY  
    proposalPackage.proposalPackageID

My problem seems to be that the subquery in the Exists clause returns 95 rows (as it should) limiting the results to those with a status of 50.

As I understand the EXISTS clause, the results should be limited to those records that "exist" in the subquery that follows... right? So, in this case, if a record does not exist in the subquery, it will not exist in the final result...??

The problem is, I'm getting 112 records when there are only 95 records to choose from (or join on) in the results list of the subquery.

So, I try to limit is by adding some additional qualifiers and joins to the subquery:

SELECT        
    proposalPackage.proposalPackageID, refProposalType.name, 
    proposalPackage.title, 
    [user].lastName, [user].firstName, [user].email, [user].phone, 
    proposalReviewAction.approvalTypeID
FROM            
    proposalReviewAction, proposalPackage 
INNER JOIN
    refProposalType ON proposalPackage.proposalTypeID = refProposalType.proposalTypeID 
INNER JOIN
    proposalManagerAssignment ON proposalPackage.proposalPackageID = proposalManagerAssignment.proposalPackageID 
INNER JOIN
    [user] ON proposalManagerAssignment.userID = [user].userID
WHERE
    EXISTS (SELECT ls.* 
            FROM 
                (SELECT 
                     r.proposalPackageID,
                     r.approvalTypeID,
                     RowNr = ROW_NUMBER() OVER (PARTITION BY r.proposalPackageID ORDER BY r.reviewedDate DESC) 
                 FROM
                     proposalReviewAction AS r
                 JOIN 
                     proposalPackage AS pp ON pp.proposalPackageID = r.proposalPackageID
                 WHERE 
                     pp.proposalCallID = 7) AS ls 
            WHERE 
                ls.RowNr = 1
                AND (ls.approvalTypeID = 50)) AS distinctified
INNER JOIN 
    proposalPackage ON distinctified.proposalPackageID = proposalPackage.proposalPackageID
INNER JOIN 
    refProposalApprovalType ON distinctified.approvalTypeID = refProposalApprovalType.approvalTypeID
GROUP BY 
    proposalPackage.proposalTypeID, [user].lastName, [user].firstName, 
    [user].email, [user].phone, proposalPackage.title, refProposalType.name, 
    proposalManagerAssignment.isPrimary, proposalPackage.proposalCallID, 
    approvalTypeID, proposalPackage.proposalPackageID, proposalReviewAction.approvalTypeID
HAVING        
    (proposalManagerAssignment.isPrimary = 1) 
    AND (proposalPackage.proposalCallID = 7) 
    AND (distinctified.approvalTypeID = 50)
ORDER BY  
    proposalPackage.proposalPackageID

Now, when I add the "AS distinctified" statement with a couple of JOINS to the subquery, I get a "SYNTAX ERROR near AS" error. I also get an "Expecting ( or SELECT" at each of the "HAVING" qualifiers.

I don't think I'm making this too complicated but that remains a possibility. It seems to me it is a matter (at this point) of overlooking a character somewhere.

Thanks in advance for the assist... AGAIN!!

Answer 1

This isn't really an answer to your much more complex example, but it should explain what the root cause is hopefully?

DECLARE @x TABLE (id INT);
INSERT INTO @x SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3;
DECLARE @y TABLE (id INT);
INSERT INTO @y SELECT 1 UNION ALL SELECT 3;

--This is what you are doing
SELECT * FROM @x WHERE EXISTS (SELECT * FROM @y);

--This is what you should be doing
SELECT * FROM @x x WHERE EXISTS (SELECT * FROM @y y WHERE y.id = x.id);